SQL Join 2 tables even if there is no matching data

Question

I am working to join the contents of my tables, I am trying to join two tables while still getting the results from table 'movies' even if there is no matching record in table 'users_ratings'. But it seems not working I get only one result.

--movies Table (100 records)

CREATE TABLE IF NOT EXISTS `movies` (
  `ID` int(20) NOT NULL,
  `title_name` vachar(255) NOT NULL,
  `creation_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--ratings Table (1 records)

CREATE TABLE IF NOT EXISTS `users_ratings` (
  `ID` int(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `type_id` bigint(20) NOT NULL,
  `type_name` vachar(255) NOT NULL,
  `score` int(11) NOT NULL,
  `creation_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--My Query

SELECT name_table.*, ur.type_id, count(ur.type_id) vote_count
FROM `movies` name_table 
left JOIN users_ratings ur ON name_table.id = ur.type_id
WHERE ur.type_name= 'movies'
GROUP BY ur.type_id
Order BY vote_count DESC

--Query Results

ID    title_name  creation_date        type_id  vote_count
1     Avatar      2014-05-20 00:00:00  1        1

--Results I'm trying to get (Should I get 100 records)

ID    title_name      creation_date        type_id  vote_count
1     Avatar          2014-05-20 00:00:00  1        1
2     Avengers        2014-05-20 00:00:00  NULL     NULL
3     Ant-Man         2014-05-20 00:00:00  NULL     NULL
4     Jurassic World  2014-05-20 00:00:00  NULL     NULL
5     Rampage         2014-05-20 00:00:00  NULL     NULL
6     Black Panther   2014-05-20 00:00:00  NULL     NULL
7     Tombe Raider    2014-05-20 00:00:00  NULL     NULL
8     Deadpool        2014-05-20 00:00:00  NULL     NULL
9     Pacific Rim     2014-05-20 00:00:00  NULL     NULL

Madhur Bhaiya · Answer 1 · 2018-09-22T14:34:26.457

1

Since, you have put an explicit Where condition of ur.type_name = movies, so it filters out the result after the Left join. You need to shift your Where condition to LEFT JOIN ON matching.

Try the following instead:

SELECT name_table.*, ur.type_id, count(ur.type_id) vote_count
FROM `movies` name_table 
left JOIN users_ratings ur ON name_table.id = ur.type_id 
                              AND ur.type_name= 'movies'
GROUP BY name_table.id 
Order BY vote_count DESC

Note: As pointed out by @Strawberry, you should avoid using wildcard (*) based Select; and get only specific columns as required by your application code.

Additionally, you may read Why is SELECT * considered harmful?

edited Sep 22 '18 at 14:34

answered Sep 22 '18 at 14:28

Madhur Bhaiya

28,155
10
49
57

...and only select the columns you actually want returned – Strawberry Sep 22 '18 at 14:30
@MappyLord check the edited answer. Your group by was also wrong – Madhur Bhaiya Sep 22 '18 at 14:34

score 0 · Accepted Answer · answered Sep 22 '18 at 14:32

In addition to your join being wrong, I think the group by is too.

I think you want:

SELECT m.*, MAX(ur.type_id), COUNT(ur.type_id) as vote_count
FROM movies m LEFT JOIN
     users_ratings ur
     ON m.id = ur.type_id AND ur.type_name= 'movies'
GROUP BY m.id
ORDER BY vote_count DESC;

In general, using something like SELECT m.* in a GROUP BY query would be incorrect. This is the one case where this is allowed, because id is unique in the movies table.

Note that type_id now has an aggregation function in the SELECT.

score 0 · Answer 3 · answered Sep 22 '18 at 21:35

Try it: AND ( ur.type_name = 'movies' OR ur.type_name IS NULL). The null results will be displayed.

SELECT name_table.*, ur.type_id, count(ur.type_id) vote_count
FROM `movies` name_table 
left JOIN users_ratings ur ON name_table.id = ur.type_id 
                    AND ( ur.type_name = 'movies' OR ur.type_name IS NULL)
GROUP BY name_table.id 
Order BY vote_count DESC;`

SQL Join 2 tables even if there is no matching data

3 Answers3