SQL & PL/SQL

Hi!

I'm learning SQL and I have lot of doubts but I think with this example I can generalize them.

I have the tables:

book {idbook (PK), namebook}
auhor {idauthor (PK), nameauthor, idcountry (FK)}
authorship {idauthor (FK), idbook (FK)} (both the same constraint PK)
country {idcountry (PK), namecountry}

I want the name of the books that have authors from Canada.

I assumed that a correct query would be:

SELECT namebook FROM book NATURAL JOIN (authorship NATURAL JOIN (author NATURAL JOIN country)) WHERE country.namecountry = 'Canada';

The result that I expected was:

Book3
Book5

but this query returns me all books that have relations in authorship (with authors from any country), 2 times!! like:

book2
book3
book4
book5
book2
book3
book4
book5

the best I can do to get my correct result is:

SELECT namebook FROM book NATURAL JOIN (authorship NATURAL JOIN author) WHERE author.idcountry = 2;

But of course I can't use this one...

Does anyone can explain me what is happening?

Thanks a lot!

Edited by: user12040235 on 15/10/2009 09:37

Edited by: user12040235 on 15/10/2009 09:51