Home » SQL & PL/SQL » SQL & PL/SQL » simple join query
simple join query [message #376551] Wed, 17 December 2008 16:18 Go to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I have tow tables

Table A Table B


B has Fk from A with unique and not nullable contraint

I want fetch all A's without B (I mean A which is not in B)
I want to select only the A's which are not in Table B Please help me write this query .





Re: simple join query [message #376552 is a reply to message #376551] Wed, 17 December 2008 16:52 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Try..

OUTER JOIN WHERE COLUMN IS NOT NULL

MINUS


NOT IN

[Updated on: Wed, 17 December 2008 16:54]

Report message to a moderator

Re: simple join query [message #376553 is a reply to message #376552] Wed, 17 December 2008 17:20 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
can you be more specific ,please i am dumb with sql should I go with outer join or minus or not null ?if possible can you giove me example
Re: simple join query [message #376556 is a reply to message #376552] Wed, 17 December 2008 17:44 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
here is my tought

select * from a where a.id not in (
select a.id from a, b where ai.id=b.fk_id)
)
Iam assuming there is a better way to write this and this will be very slow .
Please tell me what other ways we have ?


Re: simple join query [message #376569 is a reply to message #376551] Wed, 17 December 2008 21:25 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Then Try

MINUS Or

WHERE NOT EXISTS

Smile
Rajuvan.
Re: simple join query [message #376572 is a reply to message #376556] Wed, 17 December 2008 21:43 Go to previous messageGo to next message
danish_fsd@yahoo.com
Messages: 38
Registered: February 2008
Location: Pakistan
Member
here is the example.

select a.id from a
minus
select distinct b.id from b

Regards
Danish
Re: simple join query [message #376595 is a reply to message #376556] Thu, 18 December 2008 00:03 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
miroconnect@yahoo.com wrote on Thu, 18 December 2008 00:44
here is my tought

select * from a where a.id not in (
select a.id from a, b where ai.id=b.fk_id)
)
Iam assuming there is a better way to write this and this will be very slow .
Please tell me what other ways we have ?




You almost got the idea, except that you don't need the join to table a in the "not in" part.
You want all the records where a.id not in the collection of fk_id-s in b.
Previous Topic: Need help!!!
Next Topic: File on Client Machine
Goto Forum:
  


Current Time: Sun Dec 04 08:58:40 CST 2016

Total time taken to generate the page: 0.09072 seconds