CODEWITHSUNDEEP
mysql
Milika
Milika

Reputation: 43

MySQL multiple sub-queries optimization

I have 3 tables
Table1 has id cell
Table2 has id cell
Table3 has id1 cell -> corresponds to Table1.id
Table3 has id2 cell -> corresponds to Table2.id

I need a result that count how many times the pair of id1 and id2 appeared in Table3, also for a result that did not appear.

So cant use simple Select * from table3 group by id1, id2.

I used

SELECT *
FROM table1, table2, 
    (SELECT COUNT(*) AS count
    FROM table3
    GROUP BY id1, id2)
GROUP BY table1.id, table2.id

but it is slow as molasses and it does not work.

Upvotes: 0

Views: 35

Answers (2)

Mureinik
Mureinik

Reputation: 311308

I'd cross-join table1 and table2, and then left join this on table3, and count an expression to check if combination exists:

SELECT     t1.id,
           t2.id,
           COUNT(CASE WHEN t3.id1 IS NOT NULL AND t3.id2 IS NOT NULL THEN 1 END)
FROM       t1
CROSS JOIN t2
LEFT JOIN  t3 ON t1.id = t3.id1 AND t2.id = t3.id2
GROUP BY   t1.id, t2.id

Upvotes: 1

Shidersz
Shidersz

Reputation: 17190

Try next query:

1) First, we cross join table1 and table2 for get all combination tuples.

2) Second, we left join to table3.

3) We count the number of rows obtained for each tuple, counting not null values on table3->id1 (This assume that tuples of types <null, id> or <id, null> don't exists on table3)

SELECT
   t1.id, t2.id, COUNT(t3.id1) AS count
FROM
   table1 AS t1
CROSS JOIN
   table2 AS t2
LEFT JOIN
   table3 AS t3 ON t3.id1 = t1.id AND t3.id2 = t2.id
GROUP BY
   t1.id, t2.id

Upvotes: 1

Related Questions