Home » SQL & PL/SQL » SQL & PL/SQL » Select data that is not in other table(Merged)
Select data that is not in other table(Merged) [message #192332] Mon, 11 September 2006 22:37 Go to next message
NGSOOKFONG
Messages: 16
Registered: August 2006
Junior Member

Dear sir,


Below is my SQL scripts, i want to pull the data from table1 and link to table2 when there is no data exists in table2. Please help .Thanks



select a.part, a.qty
from table1 a, table2 b

eg.


table1
----------
abc 10
aaa 15
cde 1
eee 1

table2
-----------
aaa
eee

From the above table the actual output that i need is

result
-------
abc 10
cde 1



regards
mabel
Re: how to select data not exists in the table [message #192337 is a reply to message #192332] Mon, 11 September 2006 23:17 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
Try his SQL statement.


select part, qty
from table1
where part not in (select part from table2)
/


Liza
Select table not exist in a table [message #192387 is a reply to message #192332] Tue, 12 September 2006 02:26 Go to previous messageGo to next message
NGSOOKFONG
Messages: 16
Registered: August 2006
Junior Member

Dear sir,

Please help to correct my SQL scripts.


select a.part, a.tran_id , a.qty
from table1 a
where a.part not in (select b.part, b.tran_id
from table2 b)


table 1
----------
aaa 11322 10
aaa 11221 -9
aaa 11112 3

table 2
-------
aaa 11322 10


The correct output that i want is:

output
-------

aaa 11221 -9
aaa 11112 3


Please help to check the scripts . Thanks Mabel.

Re: Select table not exist in a table [message #192391 is a reply to message #192387] Tue, 12 September 2006 02:44 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
You may try this:

select a.part, a.tran_id , a.qty
from table1 a
where (a.part,a.tran_id) not in (select b.part, b.tran_id
from table2 b)



Liza
Re: Select table not exist in a table [message #192488 is a reply to message #192391] Tue, 12 September 2006 10:19 Go to previous messageGo to next message
vjaynarwade
Messages: 5
Registered: September 2006
Location: Ramanthapur
Junior Member

hello,

u can do like this


select a.part(etc) from table1 a,table2 b where a.qty not in
(select b.qty from table2 b);

chek out , it may solve u r pbm.
Re: Select table not exist in a table [message #192507 is a reply to message #192488] Tue, 12 September 2006 12:21 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
1. This is incorrect in so many way.
2. This is a cartesian product and poor query.
3. You type too fast as you have missed many many letters in your words, or you just need a new keyboard because a lot of letter don't seem to be on your keyboard.
Re: Select table not exist in a table [message #192924 is a reply to message #192387] Thu, 14 September 2006 04:36 Go to previous message
sati.sh
Messages: 3
Registered: July 2006
Location: hyderabad
Junior Member
Hi Try to do like this

select * from table1 a
where
a.part not in
(
select b.part from table1 b, table2 c where
b.part = c.part
or
b.tran_id = c.tran_id)
Previous Topic: ORA-04021 Error while recreating a Package
Next Topic: using in and = conditions
Goto Forum:
  


Current Time: Sun Dec 04 06:44:10 CST 2016

Total time taken to generate the page: 0.08464 seconds