Fetch certain records from db using SQL

Question

I have a table with the multiple records where PLACE_ORDER entry is not there i.e order#594886579291. I want to fetch only order where PLACE_ORDER row is not there like 594886579291.

If I do a simple query like

SELECT * FROM ORDER_STATUS os WHERE os.SERVICE_NAME != 'OC'.

It returns orders 594886579290 & 594886579295 without OC. I need only 594886579291.

I tried self join but no luck.

FYI- I am using DB2.

why -1 ? I did research and also put self joins, NOT exists but my data was getting repeated so I asked this question. — Vijay Kumar Rajput, Feb 11 '19 at 07:19
What is wanted is not clear & it is not obvious from your example. "fetch only order where PLACE_ORDER row is not there" is not clear. Use enough words, sentences & references to parts of examples to say what you mean. And give the rest of a [mcve]. Show parts you can do. Please use text, not images/links, for text, including tables & ERDs. Textual content in images/links cannot be searched for & cannot be cut & pasted. Also this is an easily found faq if you would google a clear concise statement of what you want without your particular strings/names. Please clarify via edits, not comments. — philipxy, Feb 16 '19 at 19:21

score 0 · Accepted Answer · answered Feb 09 '19 at 13:13

If you just want the order numbers:

SELECT order_number
FROM ORDER_STATUS os 
GROUP BY order_number
HAVING SUM(CASE WHEN os.SERVICE_NAME = 'OC' THEN 1 ELSE 0 END) = 0;

If you want the full order details, then use NOT EXISTS:

SELECT os.*
FROM ORDER_STATUS os 
WHERE NOT EXISTS (SELECT 1
                  FROM ORDER_STATUS os2
                  WHERE os2.order_number = os.order_number AND
                        os2.SERVICE_NAME = 'OC'
                 );

score 0 · Answer 2 · answered Feb 16 '19 at 17:04

Another way:

SELECT ORDER_NUMBER FROM ORDER_STATUS WHERE SERVICE_NAME != 'OC'
MINUS
SELECT ORDER_NUMBER FROM ORDER_STATUS WHERE SERVICE_NAME  = 'OC'

This can also be used as a sub select to a larger query:

  SELECT * 
  FROM ORDER_STATUS
  WHERE ORDER_NUMBER IN (
     SELECT ORDER_NUMBER FROM ORDER_STATUS WHERE SERVICE_NAME != 'OC'
     MINUS
     SELECT ORDER_NUMBER FROM ORDER_STATUS WHERE SERVICE_NAME  = 'OC'
     )

Fetch certain records from db using SQL

2 Answers2