March 8, 2005 at 7:09 pm
Is it possible to return 1 record only? Basically, CompanyID of 1 has 1 or many phones. In this case, it has 2 phones. 1 is a work phonetype and the other is a mobile phonetype. At the moment it returns 2 records, 1 for the work phone and 1 for the mobile phone which is fine but I would like to return 1 record with the work phone & mobile phone.
It has something to do with the Phone Table having 2 records for CompanyId of 1 however, I thought the subqueries as I have it below would have returned just 1 record only!
SELECT Co.CompanyName, C.FirstName, C.SurName, CT.ContactType, P.AreaCode, PT.PhoneType,
(SELECT P.PhoneNumber
FROM Phone P INNER JOIN Contact C ON P.ContactID = C.ContactID
WHERE C.ContactID = 1
AND P.PhoneTypeID = 1) AS 'WorkPhone', --P.PhoneNumber
(SELECT P.PhoneNumber
FROM Phone P INNER JOIN Contact C ON P.ContactID = C.ContactID
WHERE C.ContactID = 1
AND P.PhoneTypeID = 2) AS 'Mobile'
FROM Company Co INNER JOIN Contact C ON Co.CompanyID = C.CompanyID
INNER JOIN ContactTypes CT ON C.ContactTypeID = CT.ContactTypeID
INNER JOIN Phone P ON C.ContactID = P.ContactID
INNER JOIN PhoneTypes PT ON P.PhoneTypeID = PT.PhoneTypeID
WHERE Co.CompanyID = 1
AND C.ContactID = 1
March 8, 2005 at 9:06 pm
Read BOL on TOP
SELECT TOP 1 <column List>
March 8, 2005 at 9:44 pm
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply