MySQL Left join WHERE table2.field = "X"
Solution 1
Simply add your contraint custom.key='votes'
to the LEFT JOIN
SELECT *
FROM pages LEFT JOIN custom
ON pages.page_id=custom.page_id AND custom.key='votes'
WHERE pages.type IN('type_a','type_b','type_c') ;
Solution 2
I'd do it like this:
SELECT *
FROM pages
LEFT JOIN
( SELECT * From custom where key='votes') cv
on pages.page_id = cv.page_id
WHERE pages.type IN ('type_a', 'type_b', 'type_c');
Solution 3
try changing your where condition to custom.key = 'votes' OR custom.key is null.
Ivan Dokov
Web developer since 2006. Linux fan since 2011. SOreadytohelp
Updated on April 02, 2020Comments
-
Ivan Dokov about 4 years
I have the following tables:
pages:
+------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | page_id | int(11) | NO | PRI | NULL | auto_increment | | type | varchar(20) | NO | | NULL | | | categories | varchar(255) | NO | | NULL | | | title | varchar(255) | NO | MUL | NULL | | | text | longtext | NO | MUL | NULL | | +------------+--------------+------+-----+---------+----------------+
custom:
+---------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+---------+-------+ | page_id | int(10) unsigned | NO | PRI | NULL | | | key | varchar(255) | NO | PRI | NULL | | | value | longtext | NO | | NULL | | +---------+------------------+------+-----+---------+-------+
I want to join the tables in a way where:
1) all the entries from the first table are returnedLEFT JOIN custom ON pages.page_id = custom.page_id
2)pages.type IN ('type_a', 'type_b', 'type_c')
3) "key" from the second table has value "votes"custom.key = 'votes'
I made everything so far, but the third condition is the problem. If there isn't entry for
key = 'votes'
in table custom the query returns only these with entries. I want to returnNULL
if missing entries.I need
key = 'votes'
, because I have other entries for this page_id where the key is not 'votes' and this duplicates the rows from pages