CODEWITHSUNDEEP
mysql
bolvo
bolvo

Reputation: 359

mysql linked tables, return null if not present in connecting table

I have 2 tables that have a many-to-many relation, using a 3rd table. Table 1 colors:

____________
id | color | 
____________
1 | red  |     
2 | blue |    
3 | yellow |    
4 | orange |    
5 | green |

table 2 content: (Table content is not really relevant for the query, I add it for completeness.)

id | itemname 
1 | item1     
2 | item2     
3 | item3

...

table 3 is the connecting table colors_content

id | content_id | color_id
1 | 1 | 1 
2 | 1 | 2 
3 | 1 | 5 
4 | 2 | 1 
5 | 3 | 1 
6 | 4 | 4

I want to return all colors + a way to figure out which ones are already linked to the content_id I am querying, so as an example, so for item.id=1 the result should be:

colors.id | colors.color | colors_content.content_id
1 | red | 1 (this can be anything, like a boolean)
2 | blue | 1
3 | yellow | null
4 | orange | null
5 | green | 1

again: it is a many-to-many relation, I need to output exactly 1 of each color, with a way to know if the queries item is already linked to it.

I have tried joining in different ways but I can not seem to find the correct syntax that includes the null values for unlinked colors.

SELECT c.* FROM `colors` c left join colors_content cc on cc.color_id=c.id where cc.content_id=1

only returns the 3 linked colors for item 1, not the 2 others.

edit: This quite convoluted query seems to do the trick (although I am unclear why the group statement will not randomly choose between the queried id and the 0 value), but I must be missing a more obvious solution:

select * from ( SELECT c.id , c.color, cc.content_id as present FROM colors c JOIN colors_content cc ON c.id=cc.color_id WHERE cc.content_id=7 UNION select id , color, 0 as present from colors) as resulttable group by color

Upvotes: 1

Views: 59

Answers (1)

sticky bit
sticky bit

Reputation: 37472

Move the check for the content_id in the ON clause and then check if the color_id of colors_content is NULL.

SELECT c.id,
       c.color,
       cc.colorid IS NULL exists_for_content_id
       FROM colors c
            LEFT JOIN colors_content cc
                      ON cc.color_id = c.id
                         AND cc.content_id = 1;

(This assumes that (colors.id), (content.id) and (colors_content.content_id, colors_content.color_id) are not nullable and unique.)

Upvotes: 2

Related Questions