CODEWITHSUNDEEP
sqlsql-server
Rasik
Rasik

Reputation: 2410

Left Joining not returning all the records from left table

I want to get the count by doing join with the two tables, I am doing group by and left join.

But I am not getting all the row if that record doesn't match another table.

Here in the given query:

create table UserTable (
  Id integer not null,
  Name varchar(12) not null
);

insert into UserTable  values (1,  'A B');
insert into UserTable  values (2,  'A C');
insert into UserTable  values (3,  'A C A C');
insert into UserTable  values (4,  'A C C');
insert into UserTable  values (5,  'A C B');
insert into UserTable  values (6,  'A C C');
insert into UserTable  values (7,  'A C D');
insert into UserTable  values (8,  'A C E');
insert into UserTable  values (9,  'A C F');


create table LogTable (
  LogId integer not null,
  Username varchar(12) not null,
  Event varchar(12) not null
);

insert into LogTable  values (1, 'A C A C', 'Read');
insert into LogTable  values (2, 'A C F', 'Write');
insert into LogTable  values (3, 'A C F', 'Read');
insert into LogTable  values (4, 'A C C', 'Update');
insert into LogTable  values (5,'A C C', 'Read');
insert into LogTable  values (6,'A C F', 'Read');
insert into LogTable  values (7,'A C F', 'Update');
insert into LogTable  values (7,'A C F', 'Write');
insert into LogTable  values (7,'A C E','Update');
insert into LogTable  values (7,'A C F', 'Delete');
insert into LogTable  values (10,'A C B', 'Delete');
insert into LogTable  values (11, 'A C F','Copy');
insert into LogTable  values (12, 'A C B','Read');
insert into LogTable  values (13, 'A C F','Update');
insert into LogTable  values (14, 'A C F','Copy');
insert into LogTable  values (15, 'A C F','Read');
insert into LogTable  values (16, 'A C F','Update');
insert into LogTable  values (17, 'A C F','Copy');
insert into LogTable  values (18, 'A C C','Read');
insert into LogTable  values (19, 'A C D','Update');

I am doing:

SELECT COUNT(*) as Read,UT.Name, UT.Id 
FROM UserTable UT 
LEFT JOIN LogTable LT ON LT.Username = UT.Name 
WHERE LT.Event = 'Read'
GROUP by UT.Name, UT.Id

What I want is to get all the records from the UserTable, also if that doesn't exist in LogTable

If not exist make the ReadCount as 0.

Why is Left Joining not returning all the records from UserTable in this query?

Desired Output:

Id  |   Name    |   ReadCount
-----------------------------
1   |   A B     |   0
2   |   A C     |   0
3   |   A C A C |   1
4   |   A C C   |   2
5   |   A C B   |   1
6   |   A C C   |   2
7   |   A C D   |   0
8   |   A C E   |   0
9   |   A C F   |   3

Upvotes: 0

Views: 64

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You need to move the condition to the ON clause. However, you do not want COUNT(*) because you want to count the number of matches. That would be:

SELECT UT.Name, UT.Id , COUNT(LT.UserName) as num_read
FROM UserTable UT LEFT JOIN
     LogTable LT
     ON LT.Username = UT.Name AND LT.Event = 'Read'
GROUP by UT.Name, UT.Id;

That said, this type of query is often faster with a correlated subquery:

select ut.*,
       (select count(*)
        from logtable lt
        where lt.username = ut.name and lt.event = 'Read'
       ) as num_read
from usertable ut;

In particular, this can take advantage of an index on logtable(username, event).

Upvotes: 3

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

SELECT COUNT(*) as Read,UT.Name, UT.Id 
FROM UserTable UT 
LEFT JOIN LogTable LT ON LT.Username = UT.Name 
WHERE LT.Event = 'Read'
GROUP by UT.Name, UT.Id

you used WHERE LT.Event = 'Read' which makes it inner join that's why you are not getting all the rows of left table

SELECT COUNT(*) as Read,UT.Name, UT.Id 
FROM UserTable UT 
LEFT JOIN LogTable LT ON LT.Username = UT.Name 
 and  LT.Event = 'Read'

GROUP by UT.Name, UT.Id

move the condition in the on clause

Upvotes: 1

Related Questions