How to ignore records from table 1 (whoes fk-refrence must be present in table 2) but where condition does not satisfies

my query might sound simple to most of you but it did hook me up for quite a time now!

table **user**
+----+-------+
| id | name  |
+----+-------+
| 1  | Sanam |
+----+-------+
| 2  | Raj   |
+----+-------+
| 3  | bipul |
+----+-------+

table **msg**
+----+-----------+-----------+
| id | msg       | createdBY |
+----+-----------+-----------+
| 1  | Hi Raj    | 1         |
+----+-----------+-----------+
| 2  | Hi bipul  | 1         |
+----+-----------+-----------+
| 3  | Hi public | 1         |
+----+-----------+-----------+

table **msgToSpecificPeople**
+----+-------+-----------------+
| id | msgId | receiverId      |
+----+-------+-----------------+
| 1  | 1     | 2    --raj id   |
+----+-------+-----------------+
| 1  | 2     | 3    --bipul id |
+----+-------+-----------------+

Now, I want to receive the output as if raj is the receiver, then he gets public msg and 1 his received msg which is “hi raj”. I want to ignore “hi bipul msg”.

left outer join is giving me all the records.

SELECT m.msgContent AS MSG 
FROM msg AS m
left outer JOIN msgToSpecificPeople AS p ON m.id = p.msgId and p.receiverId = 2

would somebody fix this for me pls.

EDIT: also let me know how to achieve this in linq

Answer

I think you want:

select m.*
from msg m
where exists (select 1
              from msgToSpecificPeople tsp
              where m.id = p.msgId and tsp.receiverId = 2
             ) or
      not exists (select 1
                  from msgToSpecificPeople tsp
                  where m.id = p.msgId 
                 );

Advertisement

Answer