SQL Help Inner JOIN LEFT JOIN

Question

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

Have you tried anything? When you attempted to solve your problem before posting here, what issues did you come across? — dfundako
– dfundako, Commented May 20, 2016 at 13:52
rate.profileID is on the left join. Therefore it has to be ON the join so the exclusion retains your null values. so... LEFT JOIN Rate ON Pics.ID = Rate.PicID and Rate.ProfileID = 32 if you do LEFT JOIN Rate ON Pics.ID = Rate.PicID where gender='female' and Rate.ProfileID =32 then you basically make the left join an inner as you eliminate the nulls generated from the left join in the where clause. Null <> 32! — xQbert
– xQbert, Commented May 20, 2016 at 13:54
Why is row 24 24 2 58 4 42 female (last row) not in the expected results of the modified query? — Shadow
– Shadow, Commented May 20, 2016 at 13:55
@Shadow Because the 2nd row contains the Rate.ProfileID = 32, and that Pic.ID = 24, therefore it must remove ALL Pic.ID = 24, which removes the bottom row also. — user2643679
– user2643679, Commented May 20, 2016 at 14:01

Tom H · Accepted Answer · 2016-05-20 14:07:02Z

2

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

answered May 20, 2016 at 14:07

Tom H

47.6k15 gold badges90 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2643679 Over a year ago

Thanks so much, I tried the other 2, but this is the one that does what I wanted :)

Gordon Linoff Over a year ago

The query does not need the Rate table in the subquery.

Sturgus Over a year ago

It's a good answer - well laid-out. @user2643679, did my query run for you? It should do the exact same thing. Unless if SQL Server complained about the lack of aliases in mine?

Sturgus · Accepted Answer · 2016-05-20 14:14:58Z

1

@Shadow Because the 2nd row contains the Rate.ProfileID = 32, and that Pic.ID = 24, therefore it must remove ALL Pic.ID = 24, which removes the bottom row also.

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' AND Pics.ID NOT IN (
    SELECT Pics.ID 
    FROM Pics
    INNER JOIN Profiles ON Pics.ProfileID = Profiles.ID
    LEFT JOIN Rate ON Pics.ID = Rate.PicID
    WHERE Gender = 'female' AND Rate.ProfileID = 32)

ORDER BY Pics.ID

edited May 20, 2016 at 14:14

answered May 20, 2016 at 14:00

Sturgus

6864 silver badges18 bronze badges

Comments

Giorgos Betsos · Accepted Answer · 2016-05-20 14:14:40Z

0

Try this:

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
LEFT JOIN Rate AS r 
   ON Rate.ProfileID = r.ProfileID AND Rate.ID > r.ID
WHERE Gender = 'female' AND (Pics.ProfileID <> 32) AND (r.ID IS NULL)
ORDER BY Pics.ID

The last LEFT JOIN operation helps identify duplicates using this predicate in the ON clause:

Rate.ProfileID = r.ProfileID

If such duplicates exist, as is the case with Rate.ProfileID = 42, then only the one having the maximum Rate.ID is returned due to the following conditions:

Rate.ID > r.ID (`ON` clause)

and

(r.ID IS NULL) (`WHERE` clause)

edited May 20, 2016 at 14:14

answered May 20, 2016 at 14:08

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Collectives™ on Stack Overflow

SQL Help Inner JOIN LEFT JOIN

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related