CODEWITHSUNDEEP
mysqlsql
luke
luke

Reputation: 415

Select two items from a table as two different things

I have two tables: tblOrganisations and tblContacts. I have a query that brings back Organisations. I also want to bring back two contacts of different types (primary=1, alternate=2) linked to an organisation. However, I'm stuck on how to bring back multiple fields, for multiple contacts, from one table as different things.

So far 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 :)

Upvotes: 0

Views: 223

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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

Upvotes: 2

Related Questions