CODEWITHSUNDEEP
mysqljoin
jacobdo
jacobdo

Reputation: 1615

Duplicate values with multiple joins in query

I am trying to obtain comments made on a specific profile, but as soon as more than one person with a different relation (relation_id) to the profile has commented, I get duplicate, triplicate and so forth results.

Tables involved are as follows:

Users - user_id user_first_name user_last_name user_image path

Profile comments - comment_id profile_id user_id comment_body comment_date

Profile_user_relation - user_id profile_id relation_id

Relation_types - relation_id relation_name

My query is as follows:

    SELECT profile_comments.*, 
        relation_types.relation_name, 
        users.user_first_name, 
        users.user_image_path 
    FROM profile_comments 
    LEFT JOIN profile_user_relation ON profile_comments.profile_id = profile_user_relation.profile_id 
    LEFT JOIN relation_types ON relation_types.relation_id = profile_user_relation.relation_id 
    LEFT JOIN users ON profile_comments.user_id = users.user_id 
WHERE profile_comments.profile_id = :profileId

Thank you!

Upvotes: 0

Views: 27

Answers (1)

Dmitry F
Dmitry F

Reputation: 1670

Didn't test it out, but I think it should work, or at least to get you a little bit closer. 

SELECT 
        C.*, 
        RT.relation_name, 
        U.user_first_name, 
        U.user_image_path 
FROM profile_comments      AS C 
JOIN users                 AS U  USING(user_id) 
JOIN profile_user_relation AS UR USING(user_id, profile_id) 
JOIN relation_types        AS RT USING(relation_id)


WHERE C.profile_id = :profileId

Hope that will be a bit helpful.

Upvotes: 2

Related Questions