febri qa febri qa - 7 months ago 10
PHP Question

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

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

You can use the following query:

SELECT * 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/24

Comments