user2643679 user2643679 - 6 months ago 20
MySQL Question

SQL Help Inner JOIN LEFT JOIN

My query is:

SELECT Pics.ID, Pics.ProfileID, Pics.Position, Rate.ID as RateID, Rate.Rating, Rate.ProfileID, Gender
FROM Pics
INNER JOIN Profiles ON Pics.ProfileID = Profiles.ID
LEFT JOIN Rate ON Pics.ID = Rate.PicID
WHERE Gender = 'female'
ORDER BY Pics.ID


And results are:

ID ProfileID Position RateID Rating ProfileID Gender
23 24 1 59 9 42 female
24 24 2 33 8 32 female
23 24 1 53 3 40 female
26 24 4 31 8 32 female
30 25 4 30 8 32 female
24 24 2 58 4 42 female


Now I want to do another query which would be:
If Rate.ProfileID = 32, remove any rows that contain that same Pics.ID

so left with:

ID ProfileID Position RateID Rating ProfileID Gender
23 24 1 59 9 42 female
23 24 1 53 3 40 female


and also remove any duplicate Pics.ID so just one of the above as they are both = 23 so left with :

23 24 1 59 9 42 female or 23 24 1 53 3 40 female

Answer

You should probably get rid of "magical numbers", like 32. That said, I think that this will give you what you need.

SELECT
    P.ID,
    P.ProfileID,
    P.Position,
    R.ID as RateID,
    R.Rating,
    R.ProfileID,
    PR.Gender
FROM
    Pics P
INNER JOIN Profiles PR ON PR.ID = P.ProfileID
LEFT JOIN Rate R ON R.PicID = P.ID
WHERE
    PR.Gender = 'female' AND
    NOT EXISTS (
        SELECT *
        FROM Pics P2
        INNER JOIN Profiles PR2 ON PR2.ID = P2.ProfileID
        INNER JOIN Rate R2 ON R2.PicID = P2.ID AND R2.ProfileID = 32
        WHERE
            P2.ID = P.ID
    )
ORDER BY
    P.ID
Comments