CODEWITHSUNDEEP
mysqlpostgresql
Justin J
Justin J

Reputation: 832

Remove row from mysql selection query

I have two tables 

tbl_comment
commnet_id|commnt
1         |test1
2         |test2
3         |test3   

tbl_rejection
rej_id|done_by|comment_id|
1     | admin |1

I am listing all my comments and i need to exclude the rejection comment (comment id :1) from the select query

select * from tbl_comment
join tbl_rejection on tbl_rejection.comment_id = tbl_comment.comment_id
where tbl_rejection.rej_id is null

Can anyone help

Upvotes: 0

Views: 37

Answers (2)

Bjarni Ragnarsson
Bjarni Ragnarsson

Reputation: 1781

Join will only return records with values in both tables. You need left join to return all records in first table and null in the second table where no matching record exists.

select * from tbl_comment
left join tbl_rejection on tbl_rejection.comment_id = tbl_comment.comment_id
where tbl_rejection.rej_id is null

Upvotes: 2

Abelisto
Abelisto

Reputation: 15614

not exists is your way:

select * from tbl_comment
where not exists (
    select 1 from tbl_rejection
    where tbl_rejection.comment_id = tbl_comment.comment_id);

It could be more efficient then joining tables. Just try both solutions and compare.

Upvotes: 1

Related Questions