CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
EKet
EKet

Reputation: 7314

SQL 2005 - two tables Join on some id,

Let's say I have 2 tables. I want join them so that for every account I get 1 row where the account's information is there PLUS the primaryContact's information appended to the table. Is this possible? ID's are unique keys.

ACCOUNT TABLE 

 accountid    |    name    |    income    |    primaryContact  

 123456789     Jack Johnson    120,000      Jill Johnson

CONTACT TABLE 

parentAccountid    |contactid    |    name    |    street        |    city    |    state    |    Country

 123456789           13459284      Jill Johnson    1355 Fir street  Yorba         Washington      USA

RESULT TABLE

  accountid    |    name    |    income    |    primaryContact    |    street    |    city    |    state    |    country 

 123456789     Jack Johnson    120,000      Jill Johnson           1355 Fir street   Yorba           Washington      USA

Upvotes: 1

Views: 1062

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332561

Use:

   SELECT a.accountid,
          a.name,
          a.income,
          a.primaryContact,
          c.street,
          c.city,
          c.state,
          c.country
     FROM ACCOUNT a
LEFT JOIN CONTACT c ON c.parentaccountid = a.accountid
                   AND c.name = a.primarycontact

This will show you all the accounts. If there's a primary contact, the values will be populated--otherwise the references to the CONTACT table will be NULL. If you don't want this behavior, omit the "LEFT" from the query:

   SELECT a.accountid,
          a.name,
          a.income,
          a.primaryContact,
          c.street,
          c.city,
          c.state,
          c.country
     FROM ACCOUNT a
     JOIN CONTACT c ON c.parentaccountid = a.accountid
                   AND c.name = a.primarycontact

See this link for a visual representation of the different JOINs...

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453057

SELECT a.accountid     ,
       a.name          ,
       a.income        ,
       a.primaryContact,
       c.street        ,
       c.city          ,
       c.state         ,
       c.country
FROM   account a
       JOIN contact c
       ON     a.accountid      = c.parentAccountid
       AND    a.primaryContact = c.name

Upvotes: 2

Related Questions