Posted by Erland Sommarskog on 08/24/07 21:46

JJ297 (nc297@yahoo.com) writes:
> Having problems joining three tables. I only want the Title field but
> need them to give me the correct data.
>
> Here are my three tables and their fields.
>
> 1. Titles
> TitleID
> Title
> [description]
>
>
> 2. Classifications
> ClassificationID
> [Description]
>
> 3. Titleclassification
> ClassificationID
> TitleID
>
> This is my stored procedure thus far but getting incorrect syntax
> error.
>
> select Titles.Title
> From Titles
> Inner Join Titleclassification on classifications.classificationid =
> titleclassification.classificationid
> Join titlecassification.titleid = titles.titleid

The form for a three-way join would be:

SELECT ...
FROM tbl1 t1
JOIN tbl2 t2 ON t1.somecol = t2.somecol
JOIN tbl3 t3 ON t2.someothercol = t3.someothercol

This pattern should give you some idea to write the query. Notice that I
use aliases. This helps to make the query less verbose. If you use the
table names as prefixes, you can easily lose the sight of the forest
for all the trees.

However, I think your correct query needs to use exists instead:

SELECT T.TitleID
FROM Titles T
WHERE EXISTS (SELECT *
FROM Titleclassifications TC
JOIN Classifications C
ON TC.ClassificationID = C.ClassificationID
WHERE TC.TitleID = T.TitleID)


Else you will get a lot of duplicates.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]