CODEWITHSUNDEEP
mysqlsql
user7989054
user7989054

Reputation:

Not getting the desired result (sql query)

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:

image

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?

Upvotes: 2

Views: 88

Answers (2)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

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

Upvotes: 0

The Impaler
The Impaler

Reputation: 48770

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
    )

Upvotes: 1

Related Questions