I'm need to get the [description] from the classifications table
Trying to join it to another table (titleclassification) but I don't
need info from that table. I'm getting the results but it's sending
back repeat data. Any suggestions?

SELECT
Titles.titleid, titles.title, titles.[description],
resources.quantityowned, classifications.[description]

FROM
Titles inner join resources on titles.titleid = resources.titleid,
classifications inner join titleclassification on
classifications.classificationid =
titleclassification.classificationid

Thanks so much!

Without DDL for all the tables, including all keys, all I can do is
guess. In this case I guessed that the titleclassification table
includes titleid, and that the key is (titleid

SELECT A.titleid,
A.title,
A.[description],
B.quantityowned,
classifications.[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

On Wed, 24 Oct 2007 08:42:09 -0700, JJ297 <nc297 (AT) yahoo (DOT) com> wrote:

On Oct 24, 11:50 am, "Roy Harvey (SQL Server MVP)"
<roy_har... (AT) snet (DOT) net> wrote:
Ron, thanks for your help. The key for the titleclassification table
is titleclassID

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.

Any ideas?

On Wed, 24 Oct 2007 09:08:25 -0700, JJ297 <nc297 (AT) yahoo (DOT) com> wrote:

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.

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

On Oct 24, 1:05 pm, "Roy Harvey (SQL Server MVP)"
<roy_har... (AT) snet (DOT) net> wrote:
Thanks Roy I am now getting the results I need. I see what I did
wrong.

Thanks so much for your help!