Obtaining only certain values (sql)

Question

I have 3 different tables: table1, table2, table3

Table 1 contains all the different orders that were purchased

Table 2 contains the detail of every order (i mean, it contains a column called ORDER_DETAIL and the number represent an item of that order -a unique value)

Table 3 contains the workflow.. some numbers that were inside ORDER_DETAIL from Table 2 will appear here because this item must be approved to be delivered

I want to obtain all the different orders whose items did not appear in Table 3.

This picture explains everything:

This is my SQLFIDDLE: http://sqlfiddle.com/#!9/5bfc22/2

I did this query but i am not getting what i want:

select * from table1 kio
inner join table2 jio on kio.ORDER_NUMBER = jio.ORDER_NUMBER
where jio.CANCELLED = 0
and not exists (select 1 from table3 gio where jio.ORDER_DETAIL = gio.ORDER_DETAIL)

Also, how can i obtain those orders whose ORDER_DETAILs only appear on TABLE 2 AND those orders whose order_details appear in table 3 with PROCESSED = 1 and APPROVED = 1? All in the same query.

Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. — philipxy, Dec 15 '19 at 23:24
Please put all your question in your post, not just at a fiddle. Please ask 1 question per post. When you get a result that you don't expect/understand, stop trying to find your overall goal & find out what your misunderstanding is.--Isolate the first unexpected/misunderstood subexpression & its input & output & learn what misconception, typo, wrong reasoning, etc led to it. (Debugging fundamental.) For errors a [mre] includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) — philipxy, Dec 15 '19 at 23:27
Does this answer your question? [SQL - find records from one table which don't exist in another](https://stackoverflow.com/questions/367863/sql-find-records-from-one-table-which-dont-exist-in-another) — philipxy, Dec 16 '19 at 00:19

GMB · Accepted Answer · 2019-12-15T23:02:00.997

1

You can use aggregation: join table1 with table2, then left join table3, aggregate by order_number and filter on groups that have no match in table3.

select t1.id, t1.order_number
from table1 t1
inner join table2 t2 on t2.order_number = t1.order_number
left join table3 t3 on t3.order_detail = t2.order_detail
group by t1.id, t1.order_number
having count(t3.order_detail) = 0

In your DB Fiddle, this produces:

id  order_number
3   46646

Also, how can i obtain those orders whose ORDER_DETAILs only appear on TABLE 2 AND those orders whose order_details appear in table 3 with PROCESSED = 1 and APPROVED = 1? All in the same query

For this, you can just add another pair of conditions in the having clause:

having 
    count(t3.order_detail) = 0
    or (max(t3.processed) = 1 and max(t3.approved) = 1)

Yields:

id  order_number
1   78945
3   46646

edited Dec 15 '19 at 23:02

answered Dec 15 '19 at 22:45

GMB

216,147
25
84
135

Thank you very much for replying!. Why did you use 'or' instead of 'and' in the having condition? Also,is it possible to solve this without using group by? – JustToKnow Dec 15 '19 at 23:15
1

@Student_new: I understood that you want orders that either have no records in table3, or that have records with `processed = 1` and `approved = 1`. Both conditions cannot be fulfilled at the same time, hence `or`. Also, `group by` seems to me like the simplest way to handle this, why would not use it? – GMB Dec 15 '19 at 23:20
Oh, yeah. My bad. Yeah, i was trying to make it a little harder that's why i was thinking about another option. I tried but i was not able to do this without group by condition. Is that even possible? – JustToKnow Dec 15 '19 at 23:23
@Student_new: to me your requirement is an aggregate query in essence. While it may be possible to do it another way (with a series of subqueries maybe), I think that aggregation is the way to go here. – GMB Dec 15 '19 at 23:49

score 0 · Answer 2 · answered Dec 16 '19 at 00:15

I want to obtain all the different orders whose items did not appear in Table 3.

This seems like a reasonable interpretation of the question, although you add more questions later.

If so, then you don't seem to need table1:

select t2.order_number
from table2 t2 left join
     table3 t3
     on t2.ORDER_DETAIL = t3.ORDER_DETAIL
group by t2.order_number
having count(t3.ORDER_DETAIL) = 0;

Obtaining only certain values (sql)

2 Answers2