dan dan - 4 months ago 9
SQL Question

How should I have select unblocked comment on SQL

How should I have select unblocked comments
enter image description here
For example we have these tables and records,is it possible to show list of comments that one user wants to see ?

Something like this: list of comments who userId2(B) can see

Result must be:
{2,"Comment 3" & 2,"Comment 4" & 4,"Comment 6"}

Here is my wrong select:

SELECT Comments.Comment, Comments.UserId
FROM Users INNER JOIN
Comments ON Users.UserId = Comments.UserId INNER JOIN
Block ON Users.UserId = Block.BlockerId AND Users.UserId = Block.BlockedId
WHERE (Block.BlockerId <> 2) AND (Block.BlockedId <> 2) AND (Users.UserId = 2)


Also here is link of my sample DB "testDB.bak":
Click here to download(File size:200KB)

Thank you for helping me

Answer

I think you're looking for something like this:

select a.UserId, a.Comment from Comments a
left join Block b
on a.UserId = b.BlockedId
and b.BlockerId = <your user here>
where b.BlockerId is null;