Simple SQL question

8 Aug 2006 3:34 PM

Bonzol

Hey there, been doing some basic SQL for a while now, can do most
things like inner joins n such. But I am now trying to do an innerjoin
within in the pubs database that comes with SQL server. I'm trying to
return the titles in the title folder based on the authors last name.
Now the only thing that gets me here is the fact that to get these
values The author has a key that relates to key in the titleauthor
table which then relates to a key to the titles table.

My question is, how do I do an inner join that goes through 3 tables
like that.

author to titleAuthor to titles,,, then display titles.

I have no problems doing innerjoins between 2 tables, but not sure
about 3. Code examples would be great. thanx.

8 Aug 2006 3:56 PM

jeff

SELECT     dbo.authors.au_lname, dbo.titles.title
FROM         dbo.authors INNER JOIN
                      dbo.titleauthor ON dbo.authors.au_id =
dbo.titleauthor.au_id INNER JOIN
                      dbo.titles ON dbo.titleauthor.title_id =
dbo.titles.title_id

Show quoteHide quote

"Bonzol" <Bon***@hotmail.com> wrote in message
news:1155051278.188664.206100@b28g2000cwb.googlegroups.com...
> Hey there, been doing some basic SQL for a while now, can do most
> things like inner joins n such. But I am now trying to do an innerjoin
> within in the pubs database that comes with SQL server. I'm trying to
> return the titles in the title folder based on the authors last name.
> Now the only thing that gets me here is the fact that to get these
> values The author has a key that relates to key in the titleauthor
> table which then relates to a key to the titles table.
>
> My question is, how do I do an inner join that goes through 3 tables
> like that.
>
> author to titleAuthor to titles,,, then display titles.
>
>
> I have no problems doing innerjoins between 2 tables, but not sure
> about 3. Code examples would be great. thanx.
>

8 Aug 2006 4:04 PM

tommaso.gastaldi

Do you mean something like:   ?

SELECT
       a.au_fname AS "au_fname",
       a.au_lname AS "au_lname",
       t1.title AS "title"
FROM
       (titles t1
        INNER JOIN
       titleauthor t
        ON t1.title_id = t.title_id)
               INNER JOIN
              authors a
               ON a.au_id = t.au_id

or

SELECT
       a.au_fname AS "au_fname",
       a.au_lname AS "au_lname",
       t1.title AS "title"
FROM
       titles t1,
       titleauthor t,
       authors a
WHERE
       t1.title_id = t.title_id AND
       a.au_id = t.au_id

tommaso
http://cam70.sta.uniroma1.it/Community/

Bonzol ha scritto:

Show quoteHide quote

> Hey there, been doing some basic SQL for a while now, can do most
> things like inner joins n such. But I am now trying to do an innerjoin
> within in the pubs database that comes with SQL server. I'm trying to
> return the titles in the title folder based on the authors last name.
> Now the only thing that gets me here is the fact that to get these
> values The author has a key that relates to key in the titleauthor
> table which then relates to a key to the titles table.
>
> My question is, how do I do an inner join that goes through 3 tables
> like that.
>
> author to titleAuthor to titles,,, then display titles.
>
>
> I have no problems doing innerjoins between 2 tables, but not sure
> about 3. Code examples would be great. thanx.