I have a table with some data
t1
------------------
id (int)
item_id (int)
item_description (text)
showHide (enum 0,1)
Normally I show any results that have showHide flag set to '0'. I need to modify the query to also show results with showHide flag set to '1' if user has the corresponding item_id in another table, t2.
t2
----开发者_JAVA技巧--------------
id (int)
item_id (int)
usr_id (int)
Stuck here, not sure how to do this:
SELECT item_description FROM t1
// get results with showHide = 0
// AND
// showHide = 1 if t1.item_id found in t2 WHERE usr_id = 123
Just combine 2 queries with UNION
SELECT t1.item_description
FROM t1
WHERE showHide = 0
UNION
SELECT t1.item_description
FROM t1
INNER JOIN t2 ON t1.item_id = t2.item_id
WHERE showHide = 1
The more tricky way, thus less performant:
SELECT t1.item_description
FROM t1
LEFT JOIN t2 ON t1.item_id = t2.item_id
WHERE showHide = 0
OR (showHide = 1 AND t2.item_id IS NOT NULL)
SELECT
t1.item_description
FROM
t1
LEFT JOIN (
SELECT item_id FROM t2 WHERE usr_id = 123
) AS t2
USING (item_id)
WHERE
t1.showHide = 0
OR
(t1.showHide = 1 AND t2.item_id IS NOT NULL)
精彩评论