开发者

mySQL query -- conditional select

开发者 https://www.devze.com 2023-02-05 16:02 出处:网络
I have a table with some data t1 ------------------ id (int) item_id (int) item_description (text) showHide (enum 0,1)

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)
0

精彩评论

暂无评论...
验证码 换一张
取 消