Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to improve query dependent on multiple rows in another table

Given the two tables a and b, I want to execute a query on a which gives the rows that have at least one row in b with b.a_id = a.id and flag=1 and at least one row with b.a_id = a.id and flag=0.

I have a solution for this (see below), but I am wondering if someone knows a more elegant/efficient solution?

The example here is a simplified and distilled version of my real dataset. In reality, table ‘a’ has millions of rows and there are relevant indexes in place.

    CREATE table a (
      id int unsigned NOT NULL,
      PRIMARY KEY (id)
    );
    
    CREATE TABLE b (
      id int unsigned NOT NULL AUTO_INCREMENT,
      a_id int unsigned NOT NULL,
      flag tinyint NOT NULL,
      PRIMARY KEY (id)
    );
    
    INSERT INTO a VALUES (1),(2),(3),(4);
    INSERT INTO b (a_id, flag) VALUES
      (1,0),(1,1),(1,1),
      (2,0),(2,1),(2,0),
      (3,1),
      (4,0);

select * from a;

id
1
2
3
4

select * from b;

id a_id flag
1 1 0
2 1 1
3 1 1
4 2 0
5 2 1
6 2 0
7 3 1
8 4 0

Expected result of query:

id
1
2

View on DB Fiddle

Current solution:

    select * from a where
        exists (select * from b where a_id = a.id and flag = 1) 
        and exists (select * from b where a_id = a.id and flag = 0);
Tags:

2

Answers


  1. 0

    To simplify the question we can consider that both 0 & 1 flag should be available in table b for a.id.

    Solution 1

    select * from a 
where (
  select count(distinct flag) as c 
  from b 
  where b.a_id = a.id and b.flag in (0, 1) #list of flags
) = 2; # count of the unique flags

    Solution 2

    select a.id 
from a 
inner join 
    (
      select b.a_id 
      from b 
      where b.flag in (0,1) 
      group by b.a_id
      having count(distinct b.flag) = 2
    ) as x on x.a_id = a.id

    or

    select a.id 
from a 
where a.id in 
    (
      select b.a_id 
      from b 
      where b.flag in (0,1) 
      group by b.a_id
      having count(distinct b.flag) = 2
    )

    The performance of these queries needs to be checked with real data.

    Login or Signup to reply.
  2. 0 
    select a.id from
a,
(select distinct a_id from b where flag=1 ) t1,
(select distinct a_id from b where flag=0 ) t2
where a.id=t1.a_id and  a.id=t2.a_id

    or

    select 
a.id
from a,b
where a.id=b.a_id
group by a.id
having sum(case when flag=1 then 1 else 0 end)>0 
and sum(case when flag=0 then 1 else 0 end)>0
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search