SQL query left joining issue

Question

I have two tables: users and userlogs

table1
----------
id | name
----------
1 | A
2 | B
3 | C
4 | D


table2
----------
user_id | date
----------
1 | 2015-12-17
2 | 2015-12-18
3 | 2015-12-19
4 | 2015-12-20

If I do LEFT JOIN with selected date, it only gives data that exist in table 2.

SELECT r.user_id, count(r.user_id) as count
FROM table1 a LEFT OUTER JOIN table2 r ON ( r.user_id = a.id )
WHERE r.created_at BETWEEN '2015-12-17' AND '2015-12-19'
GROUP BY a.id

I get the following

Result
----------
user_id | count
----------
1 | 1
2 | 1
3 | 1

I need something like the following:

Result
----------
user_id | count
----------
1 | 1
2 | 1
3 | 1
4 | 0

I have tried many different ways, but was unsuccessful.

MySQL or MS SQL Server? (Don't tag products not involved. Some of them have a odd group by behavior...) — jarlh, Jan 21 '16 at 10:24
When LEFT JOIN, move the right side table conditions from WHERE to ON clause to get true left join result. (Else it behaves as a regular inner join.) — jarlh, Jan 21 '16 at 10:26
The general GROUP BY rule: "If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function." — jarlh, Jan 21 '16 at 10:26

Magnus · Answer 1 · 2016-01-21T10:43:14.497

2

Try moving the filter condition into the left join.

SELECT 
   a.id, 
   count(r.user_id) as count
FROM table1 a 
LEFT OUTER JOIN table2 r ON r.user_id = a.id AND 
                            r.created_at BETWEEN '2015-12-17' AND '2015-12-19'
GROUP BY a.id

edited Jan 21 '16 at 10:43

answered Jan 21 '16 at 10:31

Magnus

45,362
8
80
118

Thank you for your answer, but its not working. it gives NULL – Malarivtan Jan 21 '16 at 10:41
I just set up the test tables as you described (assuming that you mean to have a column called created_at rather than called date) and it works for me – Kickstart Jan 21 '16 at 10:55

Shakeer Mirza · Answer 2 · 2016-01-21T11:03:34.440

If your filtered column in WHERE clause specified with LEFT OUTER JOIN then the Join behave exactly same as INNER JOIN. If you do not wish to change the behaviour of LEFT JOIN to INNER JOIN then go for ON & AND condition.

    SELECT * FROM TABLE1 T1
    LEFT OUTER JOIN TABLE2 T2 ON T1.ID = T2.USER_ID
    WHERE T2.DATE BETWEEN '2015-12-17' AND '2015-12-19'

Is same as below

    SELECT * FROM TABLE1 T1
    INNER JOIN TABLE2 T2 ON T1.ID = T2.USER_ID
    WHERE T2.DATE BETWEEN '2015-12-17' AND '2015-12-19'

In your case you need to go for

    SELECT * FROM TABLE1 T1
    LEFT OUTER JOIN TABLE2 T2 ON T1.ID = T2.USER_ID 
    AND T2.DATE BETWEEN '2015-12-17' AND '2015-12-19'

score 0 · Accepted Answer · edited May 23 '17 at 12:32

0

This query should do just fine:

DECLARE @T1 TABLE
(
    id INT
    , name VARCHAR(50)
);

DECLARE @T2 TABLE
(
    [user_id] INT
    , [date] DATE
);

INSERT INTO @T1 (id, name)
VALUES (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D');

INSERT INTO @T2 ([user_id], [date])
VALUES (1, '2015-12-17'), (2, '2015-12-18'), (3, '2015-12-19'), (4, '2015-12-20');

SELECT T1.id, COALESCE(T2.Cnt, 0) AS Cnt
FROM (SELECT DISTINCT id FROM @T1) AS T1
LEFT JOIN (
    SELECT [user_id], COUNT(*) AS Cnt
    FROM @T2
    WHERE [date] BETWEEN '2015-12-17' AND '2015-12-19'
    GROUP BY [user_id]
    ) AS T2
    ON T2.[user_id] = t1.id;

It outputs what you are expecting :)

Or wrap it up a in a stored procedure like that:

CREATE PROCEDURE dbo.YourProcedure
(
    @company_id INT
    , @start_date DATE
    , @end_date DATE
)
as
SELECT T1.id
    , T1.department_id
    , COALESCE(T2.cnt, 0) AS cnt
FROM (
    SELECT DISTINCT id, department_id
    FROM users
    WHERE company_id = @company_id
    ) AS T1
LEFT JOIN (
    SELECT user_id, COUNT(*) AS cnt
    FROM userlogs
    WHERE created_at BETWEEN @start_date AND @end_date
    GROUP BY user_id
    ) AS T2
    ON T2.user_id = t1.id;

And just call it by passing variables to it. Using a stored procedure in Laravel 4 explains how to do it.

edited May 23 '17 at 12:32

Community

1
1

answered Jan 21 '16 at 10:51

Evaldas Buinauskas

13,739
11
55
107

Your query doesn't seem to be efficient, why creating temp tables? – Malarivtan Jan 21 '16 at 13:01
would it be possible to make it simple? – Malarivtan Jan 21 '16 at 13:01
@user4499992 I'm creating temporary tables just for test data you've given us. Use your tables instead of `@T1` and `@T2` – Evaldas Buinauskas Jan 21 '16 at 13:41
SELECT T1.id, T1.department_id, COALESCE(T2.cnt, 0) AS cnt FROM (SELECT DISTINCT id, department_id FROM users WHERE company_id = '33') AS T1 LEFT JOIN ( SELECT user_id, COUNT(*) AS cnt FROM userlogs WHERE created_at BETWEEN '2015-12-17' AND '2016-01-12' GROUP BY user_id ) AS T2 ON T2.user_id = t1.id – Malarivtan Jan 21 '16 at 13:42
can we make it simpel – Malarivtan Jan 21 '16 at 13:44
Well, it's simple enough. – Evaldas Buinauskas Jan 21 '16 at 13:44
I am working with laravel and converting to Eloquent is very difficult – Malarivtan Jan 21 '16 at 13:45
SELECT T1.id, T1.department_id, COALESCE(T2.cnt, 0) AS cnt FROM (SELECT DISTINCT id, department_id FROM users WHERE company_id = '33') AS T1 LEFT JOIN ( SELECT user_id, COUNT(*) AS cnt FROM userlogs WHERE created_at BETWEEN '2015-12-17' AND '2016-01-12' GROUP BY user_id ) AS T2 ON T2.user_id = t1.id – Malarivtan Jan 21 '16 at 13:45
You should've mentioned this in your question before asking. You asked for a RAW SQL Query. – Evaldas Buinauskas Jan 21 '16 at 13:45
I know and thought it would be simple when joining two tables – Malarivtan Jan 21 '16 at 13:47
Why not to wrap it up in a stored procedure with parameters? – Evaldas Buinauskas Jan 21 '16 at 13:49

SQL query left joining issue

3 Answers3