Home > Software design >  I have three tables with schema as t1[uid,name],t2[user,event],t3[eid,organizer]
I have three tables with schema as t1[uid,name],t2[user,event],t3[eid,organizer]

Time:10-26

UID,user,organizer is same data.

Now I want to retrieve data from t1 in such a way that t1.uid=t2.user but t2.user!=t3.organizer. So we can say Extract data from t1 if it is present in t2 but if data in t2 is in t3 then we have to skip.

I have worked with few queries not getting the correct answer.

select distinct uid, name 
from user,review 
where (user.uid=review.user) join event 
on review.user<>event.organizer;

select distinct uid,name 
from user,review,event 
where user.uid<>event.organizer and user.uid=review.user;

CodePudding user response:

You can use EXISTS to check if there is a matching row of each user in review and NOT EXISTS to make sure that there is no matching row in event:

SELECT u.uid, u.name 
FROM user u
WHERE EXISTS (SELECT 1 FROM review r WHERE r.user = u.uid) 
  AND NOT EXISTS (SELECT 1 FROM event e WHERE e.organizer = u.uid);
  • Related