复杂的SQL查询返回重复但变量值，即使使用关键字DISTINCT也是如此

Question

I need to return just two sets of data based on the query below but i get four instead

SELECT DISTINCT *,
    CASE WHEN likes.face_id    = persona.face_id THEN '1' ELSE '0' END liked,
    CASE WHEN dislikes.face_id = persona.face_id THEN '1' ELSE '0' END disliked,
    CASE WHEN comments.face_id = persona.face_id THEN '1' ELSE '0' END commented
    FROM persona, likes, dislikes, comments
    RIGHT JOIN tagged ON tagged.phone_id = 'FA' WHERE persona.face_id = tagged.face_id

below are the tables I am querying from

tagged table :

    phone_id    face_id      likeCount     dislikeCount    commentCount 
       FA         GA             1               1              1            
       FA         SA             1               0              0

persona table :

        face_id      name      age   
          GA         Frank     34    
          SA         Mark      24

likes table :

       face_id           phone_id
          GA                FA
          SA                FA

dislikes table :

       face_id           phone_id
          SA                FA

comments table :

       face_id           phone_id     comment
          GA                FA         good

I get four values (Four sets of data) from the above query instead of two (two sets of data) values

The data set below is the result of the above query when echoed out in php

"data" : [{"face_id":"GA","likeCount":"1","dislikeCount":"1","commentCount":"1","name":"Mark","age":"34","liked":"1","commented":"1","disliked":"1"},

{"face_id":"SA","likeCount":"1","dislikeCount":"0","commentCount":"0","name":"Frank","age":"24","liked":"0","commented":"0","disliked":"0"},

{"face_id":"GA","likeCount":"1","dislikeCount":"1","commentCount":"1","name":"GA","age":"34","liked":"0","commented":"1","disliked":"1"},

{"face_id":"SA","likeCount":"1","dislikeCount":"0","commentedCount":"0","name":"Frank","age":"24","liked":"1","commented":"0","disliked":"0"}]}

Answer 1

A much more generic approach

SELECT * ,
CASE WHEN likes.face_id    = persona.face_id THEN '1' ELSE '0' END liked,
CASE WHEN dislikes.face_id = persona.face_id THEN '1' ELSE '0' END disliked,
CASE WHEN comments.face_id = tagged.face_id THEN '1' ELSE '0' END commented
FROM tagged JOIN persona ON persona.face_id = tagged.face_id 
LEFT JOIN likes ON likes.face_id = persona.face_id AND likes.phone_id = 'FA'
LEFT JOIN dislikes ON dislikes.face_id = tagged.face_id AND dislikes.phone_id = 'FA'
LEFT JOIN comments ON comments.face_id = tagged.face_id AND comments.phone_id = 'FA'
WHERE tagged.phone_id =  'FA'

but there is a problem, the face_ids all come up as null