Posted by Roy Harvey (SQL Server MVP) on 10/24/07 17:05

On Wed, 24 Oct 2007 09:08:25 -0700, JJ297 <nc297@yahoo.com> wrote:

>The key for the titleclassification table
>is titleclassID

Then either I do not understand the purpose of the table (likely, as I
can only guess) or the table had an ID column stuck on as a key out of
blind reflex. My assumption was that it was a "junction" table used
to make a many-to-many relationship between title and classification.
Such a table would properly have a key consisting of the key column(s)
of the Titles table plus the key column(s) of the Classifications
table.

>When I entered what you wrote in query analyzer I got...
>
>The column prefix 'classifications' does not match with a table name
>or alias name used in the query.

I missed changing one reference to use the alias.

SELECT A.titleid,
A.title,
A.[description],
B.quantityowned,
D.[description]
FROM Titles as A
JOIN resources as B
ON A.titleid = B.titleid
JOIN titleclassification as C
ON A.titleid = C.titleid
JOIN classifications as D
ON D.classificationid = C.classificationid

Roy Harvey
Beacon Falls, CT

[Back to original message]