Not getting the desired result (sql query)

Question

I want to obtain all rows that contain processed = 1 and approved = 1 for the different ids but if the same id has any row with processed = 0 and approved = 0 then, i dont want to obtain anything for that specific id.

This is what i want:

Here is my query:

   select * from table1 oa
    inner join table2 kio on oa.id = kio.id_table_1
    inner join table3 joi on kio.col3 = joi.col3_id_table_2
    where joi.processed = 1
    and joi.approved = 1

SQL FIDDLE:

http://sqlfiddle.com/#!9/fc7266/3

I think the best way to solve this is using group by and having condition but it is not working for me

EDIT:

Another guy wanted to do this: "get all rows that just contain cols3 = 1 and cols3 =2 ONLY , (just values 1 and 2) and the output will be : "A" for the table given, i dont want to have C because it has another values of cols 3 != of 1 or 2"

Col 1 Col 2 Col 3
1       A     1
2       A     2
3       B     1
4       C     1
5       C     2
6       D     1 
7       C     3

and he solved his problem by using this query:

select col2
from yourtable
group by col2 
having sum(col3=1) > 0 
  and sum(col3=2) > 0
  and sum(col3 not in (1,2)) = 0

Is it possible to do that in my case?

You want to just get `id` value from table1 ? Because GROUP BY will restrict the result-set to one row for a particular `id`. So getting all the rows from table 2 for a specific `id` won't happen if you use `Group By` — Madhur Bhaiya, Sep 29 '19 at 15:29
Also, what happens if an `id` have pair (1,0) or (0,1) for (processed, approved) in table 3 ? Do you still select that `id` ? — Madhur Bhaiya, Sep 29 '19 at 15:30
Not exactly but could you please tell me whats going on in the other case (Why the other guy used group by in the other case? - in my edit question) — , Sep 29 '19 at 15:32

score 1 · Answer 1 · answered Sep 29 '19 at 15:14

1

You can add the extra condition:

select * from table1 oa
inner join table2 kio on oa.id = kio.id_table_1
inner join table3 joi on kio.col3 = joi.col3_id_table_2
where joi.processed = 1
  and joi.approved = 1
  and oa.id not in (
    select oa.id from table1 oa
    inner join table2 kio on oa.id = kio.id_table_1
    inner join table3 joi on kio.col3 = joi.col3_id_table_2
    where joi.processed = 0
      and joi.approved = 0
    )

answered Sep 29 '19 at 15:14

The Impaler

45,731
9
39
76

Thank you for replying!. Dude, is it possible to solve this by using group by? – Sep 29 '19 at 15:16
When you *aggregate* (that is when using `GROUP BY`) you get grouped rows, not the flat data anymore: that is, you would get a different result. – The Impaler Sep 29 '19 at 15:18
Take a look at this: https://stackoverflow.com/questions/37759228/sql-select-the-only-rows-with-only-a-certain-values-in-them. I was trying to do something like that but i failed – Sep 29 '19 at 15:19
Dude, i've updated my question. I am a little confused – Sep 29 '19 at 15:25

Madhur Bhaiya · Accepted Answer · 2019-09-29T15:41:07.720

You can use a Correlated Subquery with Not Exists():

select * from table1 oa
inner join table2 kio on oa.id = kio.id_table_1
inner join table3 joi on kio.col3 = joi.col3_id_table_2
where joi.processed = 1
and joi.approved = 1
and not exists (select 1 
                from table3 t3 
                where t3.processed = 0 
                      and t3.approved = 0 
                      and t3.col3_id_table_2 = joi.col3_id_table_2)

DB Fiddle Demo

Since you want to understand the GROUP BY approach; it works on conditional filtering using HAVING clause. MySQL implictly typecasts a boolean result from a conditional expression, i.e., true/false to 1/0. So, you can basically get all the id values, which has atleast one row with processed = 1 and approved = 1, but no row with processed = 0 and approved = 0, using the following:

SELECT col3_id_table_2 
FROM table3 
GROUP BY col3_id_table_2 
HAVING
  -- For every row in the group, check if processed = 1 and approved = 1
  -- If true, it gets typecasted to integer 1
  -- MySQL then sums them up. We need atleast one row, so > 0 condition
  SUM(t3.processed = 1 AND t3.approved = 1) > 0

  AND 

  -- For every row in the group, check if processed = 0 and approved = 0
  -- If false, it gets typecasted to integer 0
  -- MySQL then sums them up. We don't need a single such row, so = 0 condition
  SUM(t3.processed = 0 AND t3.approved = 0) = 0

Now, we can use this as a subquery (Derived Table) to get the id values, and then use it to get the corresponding rows from table1 and table2:

select * from table1 oa
inner join table2 kio on oa.id = kio.id_table_1
inner join 
(select col3_id_table_2
 from table3 
 group by col3_id_table_2
 having 
   sum(processed = 1 and approved = 1) > 0
   and sum(processed = 0 and approved = 0) = 0
 ) dt on dt.col3_id_table_2 = oa.id

DB Fiddle Demo

Thank you for replying!. Dude,i've updated my question. Could you please explain that to me? Using group by? — , Sep 29 '19 at 15:24
@Neway check the updated answer. provides another approach and explanation using `group by` — Madhur Bhaiya, Sep 29 '19 at 15:41

Not getting the desired result (sql query)

2 Answers2