luke luke - 8 months ago 19
SQL Question

Select two items from a table as two different things

I have a query that brings back Organisations (from tblOrganisations), and i want to bring back two contacts (from tblContacts) of different types (primary=1, alternate=2) linked to their organisation, but i'm stuck on how to bring back multiple fields for multiple contacts from one table as different things beyond what i've got below where i can get their ReferenceID's as PrimaryID and SecondaryID.

SELECT tblOrganisations.* ,
( SELECT tblContacts.ReferenceID FROM tblContacts WHERE tblOrganisations.ReferenceID = tblContacts.tblOrganisations_ReferenceID AND tblContacts.tblContactTypes_ReferenceID = 1 ) AS PrimaryID ,
( SELECT tblContacts.ReferenceID FROM tblContacts WHERE tblOrganisations.ReferenceID = tblContacts.tblOrganisations_ReferenceID AND tblContacts.tblContactTypes_ReferenceID = 2 ) AS SecondaryID
FROM tblOrganisations


The above query gets me the organisation, and the ReferenceID's of their contacts from tblContacts as PrimaryID and SecondaryID for the two different types of contact i want. But i want more fields for each contact - FirstName, LastName, EmailAddress etc

I tried stuff like;

SELECT tblOrganisations.* ,
( SELECT tblContacts.ReferenceID AS PrimaryID , FirstName AS PrimaryFirstName FROM tblContacts WHERE tblOrganisations.ReferenceID = tblContacts.tblOrganisations_ReferenceID AND tblContacts.tblContactTypes_ReferenceID = 1 ),
( SELECT tblContacts.ReferenceID AS SecondaryID , FirstName AS SecondaryFirstName FROM tblContacts WHERE tblOrganisations.ReferenceID = tblContacts.tblOrganisations_ReferenceID AND tblContacts.tblContactTypes_ReferenceID = 2 )
FROM tblOrganisations


But that doesn't actually bring back anything in PrimaryID, SecondaryID, PrimaryFirstName etc

Thanks for any help or pointers :)

vkp vkp
Answer

The table with the desired values has to be joined twice.In this case it would be tblcontacts.

SELECT 
o.*,
c1.referenceid AS PrimaryID,c1.firstname as primaryfirstname,
c2.referenceid AS SecondaryID,c2.firstname as secondaryfirstname
FROM tblOrganisations o
JOIN tblContacts c1 on o.ReferenceID = c1.tblOrganisations_ReferenceID 
JOIN tblContacts c2 on o.ReferenceID = c2.tblOrganisations_ReferenceID
WHERE c1.tblContactTypes_ReferenceID = 1 and c2.tblContactTypes_ReferenceID = 2