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@yahoo.com> wrote:

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

On Oct 24, 11:50 am, "Roy Harvey (SQL Server MVP)"
<roy_har...@snet.net> wrote:
> 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 <nc...@yahoo.com> wrote:
> > 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!- Hide quoted text -
>
> - Show quoted text -

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

On Oct 24, 1:05 pm, "Roy Harvey (SQL Server MVP)"
<roy_har...@snet.net> wrote:
> On Wed, 24 Oct 2007 09:08:25 -0700, JJ297 <nc...@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

Thanks Roy I am now getting the results I need. I see what I did
wrong.

Thanks so much for your help!