MySQL JOIN empty table returns only one row

Question

I have 3 tables: photos, users, votes. I want to get all photos with usernames and votes:

SELECT
    `f`.`Photo`,
    `u`.`Name`,
            `u`.`Surname`,
    COUNT(`h`.`Id`) AS `Votes`
    FROM
        `photos` `f`
    JOIN
        `users` `u`
    ON
        `f`.`UserId` = `u`.`Id`
    LEFT JOIN
        `votes` `h`
    ON
        `f`.`Id` = `h`.`PhotoId`
    WHERE
        `f`.`Show` = '1'
    GROUP BY 
        `h`.`PhotoId`

Data:

photos:
---------------------------
|Id  | Photo     | UserId |
---------------------------
|1   | pic1.jpg  | 1      |
---------------------------
|2   | pic2.jpg  | 2      |
---------------------------
|3   | pic3.jpg  | 3      |
---------------------------

users:
---------------------------
|Id  | Name      | Surname|
---------------------------
|1   | User1     | Sur1   |
---------------------------
|2   | User2     | Sur2   |
---------------------------
|3   | User3     | Sur3   |
---------------------------

votes (is empty):
---------------------------
|Id  | PhotoId   | Date   |
---------------------------

The query above will return only one row, when there are no votes:

Photo     | Name   | Surname | Votes
-------------------------------------
pic1.jpg  | User1  | Sur1    | 0
-------------------------------------

but I'd like to get all three rows (for all three photos):

-------------------------------------
Photo     | Name   | Surname | Votes
-------------------------------------
pic1.jpg  | User1  | Sur1    | 0
-------------------------------------
pic2.jpg  | User2  | Sur2    | 0
-------------------------------------
pic3.jpg  | User3  | Sur3    | 0
-------------------------------------

SOLVED: The GROUP BY clause should be f.Id, not h.PhotoId:

SELECT
`f`.`Photo`,
`u`.`Name`,
        `u`.`Surname`,
COUNT(`h`.`Id`) AS `Votes`
FROM
    `photos` `f`
JOIN
    `users` `u`
ON
    `f`.`UserId` = `u`.`Id`
LEFT JOIN
    `votes` `h`
ON
    `f`.`Id` = `h`.`PhotoId`
WHERE
    `f`.`Show` = '1'
GROUP BY 
    `f`.`Id`

To amplify on @Ray's comment, if f.show is 1 for only one photo row, then you'll get only one row back. — Jim Garrison
– Jim Garrison, Commented Sep 12, 2011 at 6:35
Thanks for response guys, but I've made a mistake in GROUP BY clause. — peter.o
– peter.o, Commented Sep 12, 2011 at 6:42
@Peter, you are expected to tag an answer as "Accepted" rather than editing the question title ;-) — Álvaro González
– Álvaro González, Commented Sep 12, 2011 at 6:45

Ben Lee · Accepted Answer · 2011-09-12 06:42:35Z

1

You shouldn't be grouping by h.PhotoId. If your votes table is empty, this column will be NULL all the way down. But when you LEFT JOIN on the votes table, it creates one row for each vote with duplicate details for all the rows in the non-votes tables. So you can group on any unique column from another table. f.Id is a good choice. So change your last line to:

GROUP BY
    `f`.`Id`

answered Sep 12, 2011 at 6:42

Ben Lee

53.5k15 gold badges129 silver badges146 bronze badges

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

Collectives™ on Stack Overflow

MySQL JOIN empty table returns only one row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related