MYSQL INNER JOIN where attribute does not exist for one row

Question

I have one Mysql table (CONFIGS) that looks like this:

+---------+
| config  |
+---------+
| ie8     |
| Firefox |
| Chrome  |
+---------+

I have another table (PROJECTS) that looks like:

+---------+----------------+--------------+
| config  | name           | passed_count |
+---------+----------------+--------------+
| Firefox | Project 1      |            0 |
| Chrome  | Project 1      |            3 |
| Chrome  | Project 2      |            1 |
| Firefox | Project 2      |            0 |
| ie8     | Project 2      |            0 |
+---------+----------------+--------------+

I want the final result to look like:

+---------+----------------+--------------+
| config  | name           | passed_count |
+---------+----------------+--------------+
| Firefox | Project 1      |            0 |
| Chrome  | Project 1      |            3 |
| ie8     | Project 1      |            0 |
| Chrome  | Project 2      |            1 |
| Firefox | Project 2      |            0 |
| ie8     | Project 2      |            0 |
+---------+----------------+--------------+

Basically I want all PROJECTS to be associated with all CONFIGS

| ie8     | Project 1      |            0 |

This is the other field that I want to be added

I tried various LEFT JOINS, RIGHT JOINS but could not get this to work. Can anyone help me please?

Answer 1

You need to generate all the possible combinations and then use a left join. Here is an example:

select c.config, n.name, coalesce(p.passed_count, 0) as passed_count
from config c cross join
     (select distinct name from projects p) n left join
     projects p
     on p.name = n.name and p.config = c.config;