Home » SQL & PL/SQL » SQL & PL/SQL » Help in exists / not in (Oracle, 8.1.7,0.0 / Server Manager 3.1.7.0.0 / Windows Server 2003)
Help in exists / not in [message #448165] Fri, 19 March 2010 23:24 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Dear Experts,

I have the following query.
SELECT A.chs_no, a.engine_no, a.lic_no
  FROM   MROH A
 WHERE   A.code = 'O7'
         AND A.eng_no LIKE '%Z13DTJ%'
         AND NOT EXISTS
               (SELECT   NULL
                  FROM   mroj C
                 WHERE       a.id = c.rohd_id
                         AND c.JC = 'T1179'
                         AND c.STS != 'X');


I want to pick from table A which don't have the code 'T1179' table B have many codes. But I am not getting the correct result. Anyone can help.
Re: Help in exists / not in [message #448166 is a reply to message #448165] Fri, 19 March 2010 23:40 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.

Please realize that we don't have your tables & we don't have your data.
Therefore we can't run, test or improve your posted SQL.

>But I am not getting the correct result.
How do we know exactly what the correct result is?
Re: Help in exists / not in [message #448177 is a reply to message #448165] Sat, 20 March 2010 02:11 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With what you posted, assuming you are able to write the correct query for the result you want, the only thing that comes in mind is "take care of null".

Quote:
I want to pick from table A which don't have the code 'T1179' table B have many codes.

Explain this. Table A has no code 'T1179'? but you test the code in table B (actually C) in your query.
What does mean "table B have many codes"?

Regards
Michel
Previous Topic: Invalid character
Next Topic: How to get the distinct count of records from a csv file?
Goto Forum:
  


Current Time: Fri Dec 09 02:09:00 CST 2016

Total time taken to generate the page: 0.08650 seconds