SQL selects many items in a join

Question

I am doing a few selections and want to calculate the overlap. Example:

SELECT id FROM foo WHERE ... SELECT id FROM bar WHERE ... SELECT id FROM baz WHERE ... 

Call these queries a, b, and c, respectively. Suppose a gives (1,2,3,4,5), b gives (1,3,5), and c gives (4,5,6). I want to combine them and count the multiplicity. In the above example, the result I'm looking for is

 id | multiplicity ----------------- 1 | 2 2 | 1 3 | 2 4 | 2 5 | 3 6 | 1 

How to do it in MySQL5 in a single query? (Parts a, b and c may be regular or stored procedures).

Answer 1

I can’t check it at the moment, but I think it will work

 SELECT id, count(id) AS multiplicity FROM ( SELECT id FROM foo WHERE ... UNION ALL SELECT id FROM bar WHERE ... UNION ALL SELECT id FROM baz WHERE ... ) AS TablesTogether GROUP BY id 

Answer 2

Here is the formatted version of the weighted solution ( weight_Foo and weight_Bar are constants)

 SELECT id, sum(fullcount) AS multiplicity FROM ( SELECT id, weight_Foo as fullcount FROM Foo WHERE ... UNION ALL SELECT id, weight_Bar as fullcount FROM Bar WHERE ... ) AS TemporaryTableName GROUP BY id