2

Let's assume I have three tables, such as:

Table A

id | id_b | id_c
-----------------
1  |   1  |  2
2  |   2  |  1
3  |   1  |  2
4  |   2  |  3
5  |   3  |  3
6  |   2  |  1

Table B

id | name
-----------------
1  | 'One'
2  | 'Two'
3  | 'Three'

Table C

id | name
-----------------
1  | 'Alpha'
2  | 'Beta'
3  | 'Charlie'

What I need, is a count of table A on the different pairs of table B and table C, which in this example looks like this:

Result

    B   |     C      | count(table_a)
-------------------------------------
'One'   |  'Alpha'   |  0
'One'   |  'Beta'    |  2
'One'   |  'Charlie' |  0
'Two'   |  'Alpha'   |  2
'Two'   |  'Beta'    |  0
'Two'   |  'Charlie' |  1
'Three' |  'Alpha'   |  0
'Three' |  'Beta'    |  0
'Three' |  'Charlie' |  1

I managed to display the rows with non-zeros, but I cannot display the others. Thank you in advance for your help!

Tuszy
  • 23
  • 3

1 Answers1

0

You need to create a Cartesian join (all records from both tables are mapped to each record in the other table) between tables B and C to get all possible pairs, then left join on this the contents of table A and group by the pairs:

select b.name, c.name, count(a.id)
from (b join c)
left join a on b.id=a.id_b and c.id=a.id_c
group by b.name, c.name

The parentheses around b join c ensures that MySQL creates the Cartesian join first. Cartesian join in MySQL can be done by a simple inner join without a join criterion.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Actually, the parentheses are immaterial in this instance. – Strawberry May 30 '17 at 08:17
  • @Shadow I tried this already, and it didn't work, but I have a query with several where conditions. What I didn't realize, is that it matters whether they are in the _WHERE_ clause, or in the _ON_ clause. I moved every condition regarding table A into the _ON_ part, and now it works perfectly. Thanks for the help – Tuszy May 30 '17 at 08:52
  • Yep, that matters a lot, since filters in the where criteria apply to the entire joined resultset, while filters in the join condition apply to a single table. Sok szerencset :) – Shadow May 30 '17 at 09:05