sql query [message #280208] |
Mon, 12 November 2007 16:50 |
etenv
Messages: 2 Registered: November 2007
|
Junior Member |
|
|
Hi Everyone,
I'm new at the forum and I'm currently learning SQL and DBMS. I stucked in the following issue and i hope i can get any help form you guys.
What i have:
author(a_nr, a_name, a_birthdate)
book(ISBN, title, pages, year)
related(ISBN,a_nr)
i want to do some queries like for instance, select all the authors that don't have any books associated to them, or selecting the name and the number of every author.
Here is what i wrote so far.
the first one:
select * from author
where a_nr not in (
select * from related
)
alternative to that:
(select a_name from author)
except
(select a_name from related)
& the second one:
select a_name form author
where (select ISBN from authors)
Are those two correct? And if not why?
What i actually want to know/learn is how to make such queries on many-to-many relationships. I mean in that i have somewhere a relationships table that holds my primary keys of the corresponding relations. What I don't understand is, what exactly does that mean? Is the relationship building automatically a conjunction between the two entities, or should i join them first?
I would realy appreciate some help here.
|
|
|
|
|
|
Re: sql query [message #281230 is a reply to message #280323] |
Fri, 16 November 2007 03:36 |
meendra
Messages: 1 Registered: November 2007
|
Junior Member |
|
|
the first one:
select * from author
where a_nr not in (
select * from related
)
a_nr is a single colum, but select * from related will return all the columns in the related table. this is an error.
following query provid you the correct result ,what u expect
select * from author a
where not exists(select 'x' from related r
where r.a_nr = a.a_nr)
|
|
|
Re: sql query [message #281240 is a reply to message #281230] |
Fri, 16 November 2007 03:49 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Also the section about IM speak should be of some interest.
Regards
Michel
|
|
|