• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL How to not select the record

 
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
author and jackaroo
Posts: 12200
280
Mac IntelliJ IDE Firefox Browser Oracle C++ Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
M Wilson
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 12200
280
Mac IntelliJ IDE Firefox Browser Oracle C++ Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 41
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 41
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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!!!
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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'> )
 
M Wilson
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 41
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 )

 
You can thank my dental hygienist for my untimely aliveness. So tiny:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic