CODEWITHSUNDEEP
mysqlsql
Jai
Jai

Reputation: 80

MySQL Join - Retrieve Left Table Rows only if all the Right Table Rows satisfies the WHERE clause

I have two tables (Table A & Table B) in which a single parent row in 'Table A' will have multiple rows in 'Table B'. I have to retrieve rows from 'Table A', only if all the child rows in 'Table B' satisfies a WHERE clause.

Table A 

id     INT
name    VARCHAR
gender  VARCHAR

Table B

id        INT
table_A_id INT
condition  INT

Now I have to fetch the rows of 'Table A' for those which all the child rows in 'Table B' satisfies the WHERE clause 'condition=100'.

Upvotes: 1

Views: 747

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I would simply recommend:

select a.*
from TableA a 
where not exists (select 1
                  from TableB B
                  where B.table_A_id = A.id and
                        condition <> 100
                 );

If condition can be NULL, then you want:

where not exists (select 1
                  from TableB B
                  where B.table_A_id = A.id and
                        (condition <> 100 or condition is null)
                 );

Upvotes: 0

zip
zip

Reputation: 4061

THis will do it:

select * from TableA A JOIN TableA B ON B.table_A_id = A.id 
where not exists (select 1 from TableB B where B.table_A_id = A.id and condition  <> 100)

Upvotes: 0

forpas
forpas

Reputation: 164089

This query:

select table_A_id
from tableb
group by table_A_id
having sum(case when condition = 100 then 1 else 0 end) = count(*)

returns all the table_A_ids in tableb that meet the condition.
You can use it with IN like this:

select * 
from tablea 
where id in (
  select table_A_id
  from tableb
  group by table_A_id
  having sum(case when condition = 100 then 1 else 0 end) = count(*)
)

Or you can join the subquery:

select a.* 
from tablea a inner join (
  select table_A_id
  from tableb
  group by table_A_id
  having sum(case when condition = 100 then 1 else 0 end) = count(*)
) b on b.table_A_id = a.id

Note that for MySql the HAVING clause can be simplified to this:

having sum(condition = 100) = count(*)

Upvotes: 2

Related Questions