August 22, 2006 at 1:49 pm
/*
Something similar to http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=303290
But different. I used same data though.
I need only primary contacts. If primary is not available then any row will do. Each company should have only one row.
Thanks
*/
DECLARE @tblCompanies TABLE
(
companyId INT,
companyName VARCHAR(100)
)
INSERT @tblCompanies
SELECT 1, 'Worldcom Communications' UNION
SELECT 2, 'Tyco, Inc.' UNION
SELECT 3, 'Enron Energy'
DECLARE @tblContacts TABLE
(
contactId INT,
companyId INT,
contactName VARCHAR(100),
contactType VARCHAR(25)
)
INSERT @tblContacts
SELECT 1, 1, 'Billy Jones', 'Primary' UNION
SELECT 2, 1, 'Fred Johnson', '' UNION
SELECT 3, 2, 'Bob Zimmerman', 'Secondary' UNION
SELECT 4, 2, 'Carla Franks', 'Primary' UNION
SELECT 5, 2, 'Samir Ahmadinajad', 'ThirdParty' UNION
SELECT 6, 3, 'Larry Stephens', 'New' UNION
SELECT 7, 3, 'Larry Gomes', ''
/* Query */
/* Expected Results */
/*
companyId companyName contactName contactType
1 Worldcom Communications Billy Jones Primary
2 Tyco, Inc. Carla Franks Primary
3 Enron Energy Larry Stephens
*/
Regards,
gova
August 22, 2006 at 2:02 pm
Are you sure your sample data in @tblContacts is correct ?
Why do Bob Zimmerman and Carla Franks have the same Contact ID ? If these persons don't have unique Contact ID's, it's going to make it next to impossible to do this.
August 22, 2006 at 6:32 pm
Sorry. That was copy paste problem.
Contact ID is primary Key.
Data corrected in original post.
Regards,
gova
August 23, 2006 at 3:31 am
Using the query posted in the thread you are referring to, you can do this:
SELECT C.*, E_prim.contactName as [Primary], E_any.contactName as [Any],
COALESCE (E_prim.contactName,E_any.contactName) as [THE Contact]
FROM @tblCompanies C
LEFT JOIN @tblContacts E_prim ON C.companyId = E_prim.companyId AND E_prim.contactType = 'Primary'
JOIN @tblContacts E_any ON C.companyId = E_any.companyId
WHERE E_any.contactId = (select min(contactId) from @tblContacts
where companyID = C.companyID)
This should accomplish what you need, if I understood your question correctly... well, to create precisely the same output as in your post, it would need to be modified to
SELECT C.companyID, C.companyName,
COALESCE (E_prim.contactName,E_any.contactName) as [THE Contact],
CASE WHEN E_prim.companyID IS NOT NULL THEN 'Primary' ELSE '' END as contactType
FROM @tblCompanies C
LEFT JOIN @tblContacts E_prim ON C.companyId = E_prim.companyId AND E_prim.contactType = 'Primary'
JOIN @tblContacts E_any ON C.companyId = E_any.companyId
WHERE E_any.contactId = (select min(contactId) from @tblContacts
where companyID = C.companyID)
EDIT : I'm not sure this was intended in the original post, but you only show the contact type if it is primary in your expected results - and that's what I did. Modify the CASE if you want to display type of the contact always.
August 23, 2006 at 6:56 am
Thanks Vldan. That answered my question.
Regards,
gova
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply