Thursday, 30 August 2018

Mysql: The SQL subquery does not return what I expect


I have this problem to solve: If two students A and B are friends, and A likes B but not vice-versa, remove the Likes entry.


For background, the Friend table has two columns, STU1 and STU2. If they are friends, then there will be an entry showing STU1, STU2 AND STU2, STU1.
In the Likes table, if Student A likes Student B, there will be an entry for STU1, STU2, but if Student B does not like Student A, there will NOT be an entry for STU2, STU1.
So, here is what I have tried. The problem is that it still leaves two rows in the Likes table that should be out of there. Any ideas on how to solve this on?
delete from Likes
where exists
    (select F.STU1, F.STU2 from Friend F
        where exists
        (select L.STU1, L.STU2 from Likes L, Friend F where
            F.STU1 = L.STU1 and F.STU2 = L.STU2)
        )
    and not exists
        (select L.STU1, L.STU2 from Likes L, Friend F where
            F.STU1 = L.STU2 and F.STU2 = L.STU1)


edit:
with onewayfriends as (
select f.* from friend f
left outer join likes l1 on l1.stu1=f.stu1 and l1.stu2=f.stu2
left outer join likes l2 on l2.stu1=f.stu2 and l2.stu2=f.stu1
where l1.stu1 is null or l2.stu1 is null)

delete l from likes l, onewayfriends f
where l.stu1 in (f.stu1, f.stu2) and l.stu2 in (f.stu1, f.stu2)

edit 2, since it's sqllite rewrite the cte as a nested query and the delete with join as a 'where row id in..'
delete from likes where rowid in (select l.rowid from
(
  select f.* from friend f
  left outer join likes l1 on l1.stu1=f.stu1 and l1.stu2=f.stu2
  left outer join likes l2 on l2.stu1=f.stu2 and l2.stu2=f.stu1
  where l1.stu1 is null or l2.stu1 is null
) f, likes l
where l.stu1 in (f.stu1, f.stu2) and l.stu2 in (f.stu1, f.stu2))


0 comments:

Post a Comment