CODEWITHSUNDEEP
mysqlsql
Mayur Patel
Mayur Patel

Reputation: 13

Convert Same Table Nested Query to JOIN

We have to query on same table. Table 'Foo', structure is like this,

Foo
-------------
ID  MID  AID STATUS
1   12   88  A
2   12   88  B
3   13   88  A
4   13   88  B
5   14   88  A
6   15   88  A

So We need only those records who have STATUS = A but not STATUS != B. We successfully created nested query. But this table contains million records and its consuming too much time.

Working Nested Query

SELECT FO.MID FROM FOO FO WHERE FO.AID = 188 AND FO.STATUS = 'A' AND FO.MID NOT IN 
(SELECT FO.MID FROM FOO FO WHERE FO.AID = 188 AND FO.STATUS = 'B' AND FO.MID IS NOT NULL)

So how to make this fast using JOIN?

Upvotes: 1

Views: 39

Answers (2)

Adarsh
Adarsh

Reputation: 726

I you have an index on MID, then the below query may help.

SELECT FO.MID 
FROM FOO FO 
WHERE FO.AID = 88 AND FO.STATUS = 'A' AND 
      NOT EXISTS (SELECT 1 FROM FOO f WHERE fo.MID=f.MID AND F.STATUS = 'B')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

One method is aggregation:

select fo.mid
from foo fo
group by fo.mid
having sum(fo.status = 'A') > 0 and
       sum(fo.status = 'B') = 0;

If you have a table of mid, then perhaps the best method is:

select m.mid
from mids m
where exists (select 1 from foo where fo.mid = m.mid and fo.status = 'A') and
      not exists (select 1 from foo where fo.mid = m.mid and fo.status = 'B');

For performance for this query, you want an index on foo(mid, status).

Upvotes: 1

Related Questions