如何获取用户id不在表记录mysql中的选定行

Question

How get selected rows where user id is not in table record

the table like this

table_a

id subid userid
1  2     123
2  4     123

table_b

id title
1  like
2  liked
3  bookmark
4  bookmarked

i use join table to get the result if WHERE userid=123 then get result

id title
2  liked
4  bookmarked

the query

select b.id,b.title from
table_a a
LEFT JOIN table_b b
ON b.id = a.subid
WHERE a.userid = '123'

but, how if userid = '345' and not in table_a get the result too?.. the result is must like this (selected rows)

id title
1  like
3  bookmark

Thankyou

Answer 1

You can use the following query:

SELECT id, title FROM t2 LEFT JOIN t1 ON t1.subid = t2.id 
WHERE (t1.userid = 345 AND NOT userid IS NULL) OR (
    NOT EXISTS(SELECT userid FROM t1 WHERE userid = 345) AND userid IS NULL
);

The working examples you can find here: http://sqlfiddle.com/#!9/c6a446/25