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?

where did this record come from? **| ie8 | Project 1 | 0 |** — Rami Del Toro, Aug 31 '14 at 21:55
Hi, and welcome. Please show those attempts you've made. Maybe it's just an easy thing to fix. — GolezTrol, Aug 31 '14 at 21:55
Don't you have a table with projects as well? The current table you have, actually seems to be the joined table (project/browser associations) — GolezTrol, Aug 31 '14 at 21:56
| ie8 | Project 1 | 0 | came since there is no IE8 for Project 1 in PROJECTS table. This is what I want to add to final result — Neal Woodbuck, Aug 31 '14 at 22:01

score 1 · Accepted Answer · answered Aug 31 '14 at 21:57

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;

answered Aug 31 '14 at 21:57

Gordon Linoff

1,242,037
58
646
786

Was it not possible to use a cross join and then group by name and config? Cross join should already generate all combinations of the two tables or am I missing something? – Ende Neu Aug 31 '14 at 22:01
Hi, thanks for quick reply. Let me test that if this will work. – Neal Woodbuck Aug 31 '14 at 22:03
Thanks @Gorgon. I was forgetting to do a CROSS JOIN that was causing the issue. – Neal Woodbuck Sep 01 '14 at 01:02

MYSQL INNER JOIN where attribute does not exist for one row

1 Answers1