SQL Table Linking

[DVFS]

Hi, I am new at using SQL and I made a very simple query to pull a few fields from 2 different tables. Now I need to add a field from a 3rd table but I am not sure how to link them. My statement so far is:

select key1 as 'ASI#', contact1.Company, contsupp.contact as "Type", contsupref as "Web Address"
from contact1,contsupp
where contact1.accountno = contsupp.accountno and
contact1.accountno = supp.accountno
contsupp.contact = 'Web Site' and rectype = 'p'

The 3rd table I need to link is called contact2 which also has the accountno field. I cannot find any straight forward explanation to do this even tho it seems like a pretty common thing to do. I'd appreciate it if somebody can point me in the right direction. Thanks!

 

[DVFS]

BTW, I am using MS SQL 7

 

[jms8222]

You're going to want to use a join. You can find more about joining tables in Books on Line (BOL).

Your query also seems to have an error in it. You reference a supp table in the where clause, but I assume you are referring to the contsupp table.

See if this works for you:

select key1 as 'ASI#', contact1.Company, contact2.??, contsupp.contact as "Type", contsupref as "Web Address",
from contsupp
join contact1 on contact1.accountno = contsupp.accountno
join contact2 on contact2.accountno = contsupp.accountno
where contsupp.contact = 'Web Site' and rectype = 'p'

You'll want to replace the ?? with the column name in the contact2 table you wish to display. This query assumes you want to do an inner join (return only the rows for which there is an equal value in the join column). If this is not the case, then you will need to lookup outer joins in BOL.

Good Luck!

 

[DVFS]

Yes I realized I had an error in the statement after I posted it. However, this is exactly what I needed to know. Thank you very much for your help!

 

[MDXer]

SemperFIDownUnda has a great FAQ explaining JOINS, in my opinion it is more intuitive than any BOL explinations.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck