aspose file tools*
The moose likes JDBC and the fly likes SQL How to not select the record Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL How to not select the record" Watch "SQL How to not select the record" New topic
Author

SQL How to not select the record

M Wilson
Ranch Hand

Joined: May 23, 2010
Posts: 32
I don't know how to ask the question, so I'll set up an example. I just need a snippet of the sql.
The table contains the following:
ID_NUMBER PARTY_CD
1001 00
1001 10
1001 99
1002 00
1002 10
1002 30
1003 00
1003 10
I don't want the sql to return any row for ID_NUMBER 1001 because it contains the PARTY_CD = 99 (even if the other 2 don't).
It can return 1002 or 1003 because none of these have PARTY_CD = 99. Loop through the ID_NUMBER or something like that? Or some kind of subquery? Thank you very much.
Just a snippet of sql something like:
SELECT ID_NUMBER
FROM TABLE
WHERE ???
FETCH FIRST 1 ROWS ONLY
Andrew Monkhouse
author and jackaroo
Marshal Commander

Joined: Mar 28, 2003
Posts: 11525
    
100

What do you have so far?

I think you are on the right path by looking at subqueries. As a first step I would create a subquery that finds the ID_NUMBER of the records I do not want in my final query. Have you got that far?


The Sun Certified Java Developer Exam with J2SE 5: paper version from Amazon, PDF from Apress, Online reference: Books 24x7 Personal blog
M Wilson
Ranch Hand

Joined: May 23, 2010
Posts: 32
My sql may return ID_NUMBER 1001
To keep this generic as much as possible (not actual or entire sql):
Query:
SELECT ID_NUMBER
FROM TABLE01
WHERE PARTY_CD IN ('00', '10', '30')
FETCH FIRST 1 ROWS ONLY

I'm not sure how to insert the subquery into the query and currently not connected to the database right now to try and run the sql, have to wait until tomorrow:
Subquery:
(SELECT PARTY_CD
FROM TABLE01
WHERE PARTY_CD != '99')

Like this:
SELECT ID_NUMBER
FROM TABLE01
WHERE PARTY_CD IN ('00', '10', '30')
AND (SELECT PARTY_CD FROM TABLE01 WHERE PARTY_CD != '99')
FETCH FIRST 1 ROWS ONLY
Andrew Monkhouse
author and jackaroo
Marshal Commander

Joined: Mar 28, 2003
Posts: 11525
    
100

M Wilson wrote:Subquery:
(SELECT PARTY_CD
FROM TABLE01
WHERE PARTY_CD != '99')


My recommendation was that the subquery should find the IDs that you do not want. So those that do equal 99. And you want to return the ID_NUMBERS, not the PARTY_CD

M Wilson wrote:Like this:
SELECT ID_NUMBER
FROM TABLE01
WHERE PARTY_CD IN ('00', '10', '30')
AND (SELECT PARTY_CD FROM TABLE01 WHERE PARTY_CD != '99')
FETCH FIRST 1 ROWS ONLY


Which is close to what you want, assuming you change the subquery. If the subquery is returning the ID_NUMBERS you don't want, then all you need is some way of saying in your AND statement that the found ID_NUMBER is NOT IN the subquery.

Does that get you closer to a solution?
M Wilson
Ranch Hand

Joined: May 23, 2010
Posts: 32
What a goofball I am, my subquery says the same thing the query is saying, I need to select id number in the subquery if it's not in the query. I think I understand. I don't have the exact sql syntax right now since I'm not connect to see if it even works, I will post when I get it to work. Thanks!!! -M.
M Wilson
Ranch Hand

Joined: May 23, 2010
Posts: 32
Ok I got it working, this is what I want it to do:
SELECT ID_NUMBER
FROM TABLE01
WHERE PARTY_CD NOT IN (SELECT ID_NUMBER FROM TABLE01 WHERE PARTY_CD != '99')
Thanks!!!
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

Sure? This seems to do the opposite of what you originally wanted?
And you link party_cd in the main query to ID_NUMBER in the subquery.

I would have expected something like:

select....
where ID_NUMBER not in (select ID_NUMBER ..... where <and here I was expecting something else than party_cd != '99'> )


OCUP UML fundamental and ITIL foundation
youtube channel
M Wilson
Ranch Hand

Joined: May 23, 2010
Posts: 32
Yes, I ran the sql and it returned desired result. I didn't know how to ask the question, but I knew what I wanted it to do and the "Sheriff" got me on the right track.
From table:
ID_NUMBER PARTY_CD
1001 00
1001 10
1001 99
1002 00
1002 10
1002 30
1003 00
1003 10
My sql returned ID_NUMBER 1002 and 1003, not 1001 as desired.
Thanks!!!
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

I am going to check this as soon as I have a database available.
Did you realy get the desired result by executing this query?



M Wilson
Ranch Hand

Joined: May 23, 2010
Posts: 32
Yes I did. I think I may have negate the subquery that's why it works, probably not the best way to write it, I'm sure it can be written in more understanding way.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

I am going to try to figure it out without a database (paper exercise is good for analysis skills, a wise man once said).

Analysing the subquery first:

returns:

1001
1001
1002
1002
1002
1003
1003

The main query can thus be rewritten as:

returns (if I were an sql database):
1001
1001
1001
1002
1002
1002
1003
1003


Those are all the records, because each record in your table returns true for:
PARTY_CD NOT IN ( 1001 , 1001 , 1002 , 1002 , 1002 , 1003 , 1003 )

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL How to not select the record