SQL 2005 - two tables Join on some id,

Question

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

Answer 1

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

Answer 2

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...