Home » Other » Training & Certification » sql query
sql query [message #280208] Mon, 12 November 2007 16:50 Go to next message
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 #280212 is a reply to message #280208] Mon, 12 November 2007 17:05 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as enumerated in above URL.

>Are those two correct?
no
>And if not why?
Syntax Errors

Why should we test your code when you seem to be incapable or unwilling to test your code yourself?
Re: sql query [message #280269 is a reply to message #280208] Tue, 13 November 2007 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

select * from author 
where a_nr not in (
                   select * from related
                  )


a_nr is alone, * is multiple columns.
Quote:

(select a_name from author)
except 
(select a_name from related)


"except" does not exists in Oracle syntax, check minus.
a_name does not exist in related
Quote:

select a_name form author
where (select ISBN from authors)


Meaningless.

Regards
Michel

Re: sql query [message #280323 is a reply to message #280269] Tue, 13 November 2007 04:02 Go to previous messageGo to next message
etenv
Messages: 2
Registered: November 2007
Junior Member
thanks on both of you!
Re: sql query [message #281230 is a reply to message #280323] Fri, 16 November 2007 03:36 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64103
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
Previous Topic: left join on, incl a where
Next Topic: mock exams ocp(developer) 10g
Goto Forum:
  


Current Time: Sun Dec 04 14:49:13 CST 2016

Total time taken to generate the page: 0.07589 seconds