MySQL: Get all rows from a table only once and add columns from another table-CodePudding

My tables:

test_people:  test_things:  test_people_things:
id|name       id|name       id|thing_id|people_id|rating
 1|alice       1|hammer     1|    1   |    1    |  3
 2|bob         2|table      2|    1   |    2    |  1
 3|eve         3|chair      3|    2   |    1    |  0
               4|glass      4|    3   |    2    |  2

What I want: Get all rows from test_things (but each row only once!) with the rating a given person applied to that thing or NULL, if no rating has been applied by that person so far. E.g. for people.id = 1 ("alice"), I'd expect this result:

thing.name|people.name|rating
hammer    | alice     | 3
table     | alice     | 0
chair     | NULL      | NULL
glass     | NULL      | NULL

This is my best try so far:

SELECT
    test_things.name,
    test_people.name,
    test_people_things.rating
FROM
    test_things LEFT JOIN test_people_things ON test_things.id = test_people_things.thing_id
    LEFT JOIN test_people ON test_people_things.people_id = test_people.id
    AND test_people.id = 1

The result is:

thing.name|people.name|rating
hammer    | alice     | 3
table     | alice     | 0
hammer    | NULL      | 1
chair     | NULL      | 2
glass     | NULL      | NULL

Here's the sqlfiddle with the test data.

CodePudding user response：

Your method of eliminating people is the problem. You need to do it before you do the 2nd join. fiddle

SELECT
    test_things.name,
    test_people.name,
    test_people_things.rating
FROM test_things 
LEFT JOIN test_people_things ON test_things.id = test_people_things.thing_id
AND test_people_things.people_id = 1 
LEFT JOIN test_people ON test_people_things.people_id = test_people.id

Why? Since you only care about Alice, you shouldn't even try to get the names for the others. and the cardinalty between things and people things is causing your records to repeat when you only want 1 per person. your results are including the hammer for BOB but just not showing his name. To prove that just include the people_ID from test_people_things. So reducing your set to Just the "people" you want in people things solves the problem.

We can't put it on the Where cause or the "Left joins" get negated.

Now I think this still has a problem if you allow a thing and people thing to have more than 1 record in test_people_things. However I assume you have a PK Constraint on Things_ID and People_ID on the test_People_things Table. Put another way, alice can't have a rating for 2 hammers or this will not work; and we will need a way o determine which rating you'd want.

CodePudding user response：

There is one word to change in your query. You need to change the first join to RIGHT JOIN;

SELECT
    tt.name,
    tp.name,
    tpt.rating
FROM
    test_things tt 
    RIGHT JOIN test_people_things tpt ON tt.id = tpt.thing_id
    LEFT JOIN test_people tp ON tpt.people_id = tp.id
    AND tp.id = 1

returns

name    name    rating
hammer  alice   3
table   alice   0
hammer  (null)  1
chair   (null)  2