Problem with LEFT JOIN

Question

Problem with LEFT JOIN

johnsql-193053

SSCommitted

Points: 1657
More actions
July 18, 2006 at 8:56 am

#169010

I have 2 tables
Table "SchoolAttendance"
------------------------
AttendanceRuID StudentID AttendanceDate AttendanceTyID RollupCount termID
-------------- ----------- ------------------------------------------------------ -------------- ----------- -----------
15097 169 2006-05-15 00:00:00.000 4 1 NULL
15098 169 2006-05-17 00:00:00.000 4 1 NULL
15172 169 2006-04-03 00:00:00.000 1 1 NULL
15173 169 2006-04-05 00:00:00.000 1 1 NULL
15174 169 2006-04-17 00:00:00.000 1 1 NULL
15175 169 2006-04-19 00:00:00.000 1 1 NULL
15176 169 2006-05-01 00:00:00.000 1 1 NULL
15177 169 2006-05-03 00:00:00.000 1 1 NULL
15178 169 2006-05-31 00:00:00.000 1 1 NULL
16001 169 2006-02-01 00:00:00.000 1 1 NULL
16014 169 2006-02-13 00:00:00.000 1 1 NULL
16194 169 2006-02-15 00:00:00.000 1 1 NULL
16243 169 2006-06-16 00:00:00.000 1 1 NULL
16322 169 2006-02-27 00:00:00.000 1 1 NULL
16408 169 2006-07-18 00:00:00.000 3 1 NULL
(15 row(s) affected)
Table "SchoolAttendanceTy"
----------------------------
AttendanceTyID Description
-------------- --------------------------------------------------
1 Present
2 Excused Absence
3 Unexcused Absence
4 Tardy
(4 row(s) affected)
Note that studentID = 169 does not have AttendanceTyID = 2 (means Excused Absence). However, I want AttendanceTyID = 2 is
included in a JOIN ( I use LEFT JOIN ) regarless of attendanceTyID is matched or not with the SQL query:
select sar.AttendanceRuID, sar.StudentID, sar.AttendanceDate, sar.AttendanceTyID, sar.RollupCount, sat.AttendanceTyID, sat.Description
from SchoolAttendanceRU sar
left outer join SchoolAttendanceTy sat on sat.companyID=sar.companyID and sat.AttendanceTyID = sar.attendanceTyID
where sar.companyID= 1370 and sar.studentID = 169 and termID is null
The problem is the LEFT JOIN does not work, and it returns no AttendanceTyID =2 for its results like the following DTS:
AttendanceRuID StudentID AttendanceDate AttendanceTyID RollupCount AttendanceTyID Description
-------------- ----------- ------------------------------------------------------ -------------- ----------- -------------- --------------------------------------------------
15097 169 2006-05-15 00:00:00.000 4 1 4 Tardy
15098 169 2006-05-17 00:00:00.000 4 1 4 Tardy
15172 169 2006-04-03 00:00:00.000 1 1 1 Present
15173 169 2006-04-05 00:00:00.000 1 1 1 Present
15174 169 2006-04-17 00:00:00.000 1 1 1 Present
15175 169 2006-04-19 00:00:00.000 1 1 1 Present
15176 169 2006-05-01 00:00:00.000 1 1 1 Present
15177 169 2006-05-03 00:00:00.000 1 1 1 Present
15178 169 2006-05-31 00:00:00.000 1 1 1 Present
16001 169 2006-02-01 00:00:00.000 1 1 1 Present
16014 169 2006-02-13 00:00:00.000 1 1 1 Present
16194 169 2006-02-15 00:00:00.000 1 1 1 Present
16243 169 2006-06-16 00:00:00.000 1 1 1 Present
16322 169 2006-02-27 00:00:00.000 1 1 1 Present
16408 169 2006-07-18 00:00:00.000 3 1 3 Unexcused Absence
(15 row(s) affected)
Can you tell me how I need to write/improve to include non-match AttendanceTyID = 2? Thanks in advance. For example, I like a DTS (extra last row in it)
AttendanceRuID StudentID AttendanceDate AttendanceTyID RollupCount AttendanceTyID Description
-------------- ----------- ------------------------------------------------------ -------------- ----------- -------------- --------------------------------------------------
15097 169 2006-05-15 00:00:00.000 4 1 4 Tardy
15098 169 2006-05-17 00:00:00.000 4 1 4 Tardy
15172 169 2006-04-03 00:00:00.000 1 1 1 Present
15173 169 2006-04-05 00:00:00.000 1 1 1 Present
15174 169 2006-04-17 00:00:00.000 1 1 1 Present
15175 169 2006-04-19 00:00:00.000 1 1 1 Present
15176 169 2006-05-01 00:00:00.000 1 1 1 Present
15177 169 2006-05-03 00:00:00.000 1 1 1 Present
15178 169 2006-05-31 00:00:00.000 1 1 1 Present
16001 169 2006-02-01 00:00:00.000 1 1 1 Present
16014 169 2006-02-13 00:00:00.000 1 1 1 Present
16194 169 2006-02-15 00:00:00.000 1 1 1 Present
16243 169 2006-06-16 00:00:00.000 1 1 1 Present
16322 169 2006-02-27 00:00:00.000 1 1 1 Present
16408 169 2006-07-18 00:00:00.000 3 1 3 Unexcused Absence
null 169 null null null 2 Excused Absence

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

First problem, SchoolAttendanceType is on the right, not left. Try a right outer join. You may also want to drop the termId is null in the where clause.

hth,

Lynn

stax68 SSChampion Points: 11711 More actions · Answer 2

select sat.AttendanceTyID
, sat.Description
, sar.AttendanceRuID
, 169 StudentID
, sar.AttendanceDate
, isnull(sar.RollupCount,0) RollupCount
from SchoolAttendanceTy sat
left join SchoolAttendanceRU sar on sat.companyID=sar.companyID
and sat.AttendanceTyID = sar.attendanceTyID and sar.studentID = 169
where sat.companyID= 1370
and termID is null

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

johnsql-193053 SSCommitted Points: 1657 More actions · Answer 3

The query from stax68 gives me the same problem like "LEFT JOIN" same as "INNER JOIN". That means I cannot get any row for sat.AttendanceTyID = 2 (no row in table SchoolAttendanceRU as sar has sar.AttendanceTyID = 2 for this student):

AttendanceTyID Description AttendanceRuID StudentID AttendanceDate RollupCount

-------------- -------------------------------------------------- -------------- ----------- ------------------------------------------------------ -----------

1 Present 15172 169 2006-04-03 00:00:00.000 1

1 Present 15173 169 2006-04-05 00:00:00.000 1

1 Present 15174 169 2006-04-17 00:00:00.000 1

1 Present 15175 169 2006-04-19 00:00:00.000 1

1 Present 15176 169 2006-05-01 00:00:00.000 1

1 Present 15177 169 2006-05-03 00:00:00.000 1

1 Present 15178 169 2006-05-31 00:00:00.000 1

1 Present 16001 169 2006-02-01 00:00:00.000 1

1 Present 16014 169 2006-02-13 00:00:00.000 1

1 Present 16194 169 2006-02-15 00:00:00.000 1

1 Present 16243 169 2006-06-16 00:00:00.000 1

1 Present 16322 169 2006-02-27 00:00:00.000 1

3 Unexcused Absence 16408 169 2006-07-18 00:00:00.000 1

4 Tardy 15097 169 2006-05-15 00:00:00.000 1

4 Tardy 15098 169 2006-05-17 00:00:00.000 1

(15 row(s) affected)

I doubt why in his query he use function isnull() for isnull(sar.RollupCount,0) RollupCount. Why does not for sar.AttendanceTyID or for sat.AttendanceTyID???

stax68 SSChampion Points: 11711 More actions · Answer 4

What result does this give?

select sat.*

from SchoolAttendanceTy sat

where sat.companyID=1370

and sat.AttendanceTyID = 2

--if this column is in the SchoolAttendanceTy table

and sat.termID is null

------------------------

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

johnsql-193053 SSCommitted Points: 1657 More actions · Answer 5

Query of stax68

select sat.*

from SchoolAttendanceTy sat

where sat.companyID=1370

and sat.AttendanceTyID = 2

gave me:

CompanyID AttendanceTyID Description Code IsDefault IsReportable LastSortType AttendanceRollupRule RuleModifiedOn RuleModifiedBy LastRule ApplyExclusiveCount ExclusivePriorityOfAttType ApplyInclusiveCount

----------- -------------- -------------------------------------------------- -------------------------------------------------- --------- ------------ -------------------------------------------------- -------------------- ------------------------------------------------------ -------------- ----------- ------------------- -------------------------- -------------------

1370 2 Excused Absence EA 0 1 Grade 2 2006-05-26 10:44:31.343 242571 3 1 1 0

(1 row(s) affected)

And query

select sar.*

from SchoolAttendanceRU sar

where sar.companyID= 1370 and sar.studentID = 169 and termID is null

gave me:

CompanyID AttendanceRuID StudentID AttendanceDate AttendanceTyID CreatedBy CreatedOn ModifiedBy ModifiedOn ModifiedComments IsManuallyModified RollupCount TermID ModifiedRollupCount

----------- -------------- ----------- ------------------------------------------------------ -------------- ----------- ------------------------------------------------------ ----------- ------------------------------------------------------ -------------------------------------------------- ------------------ ----------- ----------- -------------------

1370 15097 169 2006-05-15 00:00:00.000 4 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL

1370 15098 169 2006-05-17 00:00:00.000 4 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL

1370 15172 169 2006-04-03 00:00:00.000 1 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL

1370 15173 169 2006-04-05 00:00:00.000 1 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL

1370 15174 169 2006-04-17 00:00:00.000 1 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL

1370 15175 169 2006-04-19 00:00:00.000 1 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL

1370 15176 169 2006-05-01 00:00:00.000 1 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL

1370 15177 169 2006-05-03 00:00:00.000 1 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL

1370 15178 169 2006-05-31 00:00:00.000 1 242571 2006-06-14 12:50:59.240 NULL NULL NULL NULL 1 NULL NULL

1370 16001 169 2006-02-01 00:00:00.000 1 242571 2006-06-15 10:53:21.553 NULL NULL NULL NULL 1 NULL NULL

1370 16014 169 2006-02-13 00:00:00.000 1 242254 2006-06-15 14:53:29.850 NULL NULL NULL NULL 1 NULL NULL

1370 16194 169 2006-02-15 00:00:00.000 1 242254 2006-06-15 15:50:40.663 NULL NULL NULL NULL 1 NULL NULL

1370 16243 169 2006-06-16 00:00:00.000 1 242254 2006-06-16 11:58:45.900 NULL NULL NULL NULL 1 NULL NULL

1370 16322 169 2006-02-27 00:00:00.000 1 242254 2006-06-21 11:51:39.587 NULL NULL NULL NULL 1 NULL NULL

1370 16408 169 2006-07-18 00:00:00.000 3 242571 2006-07-18 09:02:14.917 NULL NULL NULL NULL 1 NULL NULL

(15 row(s) affected)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Please try dropping the termId is null from the where clause using the query stax68 gave you. What does that return?

Lynn

johnsql-193053 SSCommitted Points: 1657 More actions · Answer 7

Lynn,

I cannot drop constraint "termID is null" because in table "SchoolAttendanceRU", rows with TermID NOT NULL are ones I do not want to query them. Rows with "TermID is null" are different from "TermID is NOT null" in meanings. What I am concenred here sat.AttendanceTyID = 2 must be included in the result query even though in table "SchoolAttendanceRU" as SAR for studentID = 169 does NOT have any row with sar.AttendanceTyID = 2. So, I used LEFT JOIN, but it did not work.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

I took what you posted (I don't see CompanyID anywhere, so I left it off) and did the following:

set nocount on

create table dbo.SchoolAttendance(

AttendanceRuID int,

StudentID int,

AttendanceDate datetime,

AttendanceTyID int,

RollupCount int,

termID int null

)

create table dbo.SchoolAttendanceTy (

AttendanceTyID int,

Description varchar(25)

)

insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15097,169,'2006-05-15 00:00:00.000',4,1)

insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15098,169,'2006-05-17 00:00:00.000',4,1)

insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15172,169,'2006-04-03 00:00:00.000',1,1)

insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15173,169,'2006-04-05 00:00:00.000',1,1)

insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15174,169,'2006-04-17 00:00:00.000',1,1)

insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15175,169,'2006-04-19 00:00:00.000',1,1)

insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15176,169,'2006-05-01 00:00:00.000',1,1)

insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15177,169,'2006-05-03 00:00:00.000',1,1)

insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (15178,169,'2006-05-31 00:00:00.000',1,1)

insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16001,169,'2006-02-01 00:00:00.000',1,1)

insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16014,169,'2006-02-13 00:00:00.000',1,1)

insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16194,169,'2006-02-15 00:00:00.000',1,1)

insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16243,169,'2006-06-16 00:00:00.000',1,1)

insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16322,169,'2006-02-27 00:00:00.000',1,1)

insert into dbo.SchoolAttendance (AttendanceRuID, StudentID, AttendanceDate, AttendanceTyID, RollupCount) values (16408,169,'2006-07-18 00:00:00.000',3,1)

insert into dbo.SchoolAttendanceTy values (1,'Present')

insert into dbo.SchoolAttendanceTy values (2,'Excused Absence')

insert into dbo.SchoolAttendanceTy values (3,'Unexcused Absence')

insert into dbo.SchoolAttendanceTy values (4,'Tardy')

select

*

from

dbo.SchoolAttendance sa

right outer join dbo.SchoolAttendanceTy sat

on (sa.AttendanceTyID = sat.AttendanceTyID)

where

termID is null

drop table dbo.SchoolAttendance

drop table dbo.SchoolAttendanceTy

set nocount off

I go the following results:

AttendanceRuID StudentID AttendanceDate AttendanceTyID RollupCount termID AttendanceTyID Description

-------------- ----------- ----------------------- -------------- ----------- ----------- -------------- -------------------------

15172 169 2006-04-03 00:00:00.000 1 1 NULL 1 Present

15173 169 2006-04-05 00:00:00.000 1 1 NULL 1 Present

15174 169 2006-04-17 00:00:00.000 1 1 NULL 1 Present

15175 169 2006-04-19 00:00:00.000 1 1 NULL 1 Present

15176 169 2006-05-01 00:00:00.000 1 1 NULL 1 Present

15177 169 2006-05-03 00:00:00.000 1 1 NULL 1 Present

15178 169 2006-05-31 00:00:00.000 1 1 NULL 1 Present

16001 169 2006-02-01 00:00:00.000 1 1 NULL 1 Present

16014 169 2006-02-13 00:00:00.000 1 1 NULL 1 Present

16194 169 2006-02-15 00:00:00.000 1 1 NULL 1 Present

16243 169 2006-06-16 00:00:00.000 1 1 NULL 1 Present

16322 169 2006-02-27 00:00:00.000 1 1 NULL 1 Present

NULL NULL NULL NULL NULL NULL 2 Excused Absence

16408 169 2006-07-18 00:00:00.000 3 1 NULL 3 Unexcused Absence

15097 169 2006-05-15 00:00:00.000 4 1 NULL 4 Tardy

15098 169 2006-05-17 00:00:00.000 4 1 NULL 4 Tardy

Is this what you are trying to get? If so, take my code, modify however it is needed to better match your data and test it in a test database and see what happens.

hth,

Lynn

stax68 SSChampion Points: 11711 More actions · Answer 9

can you paste the exact code you ran?

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

I just figured out the problem. Your where clause contains sar.studentID = 169.

The line with AttendanceTyID = 2 is null in the column, and fails the where condition.

Change it to where (sar.studentID = 169 or sar.studentID is null). You will need to do

this for any other columns in the where clause as well.

hth,

Lynn

stax68 SSChampion Points: 11711 More actions · Answer 11

The only change I can think of is to my previous code is to put the 'termID is null' in the join. But that shouldn't be able to increase the number of rows returned...can you post your exact code?

select sat.AttendanceTyID

, sat.Description

, sar.AttendanceRuID

, 169 StudentID

, sar.AttendanceDate

, isnull(sar.RollupCount,0) RollupCount

from SchoolAttendanceTy sat

left join SchoolAttendanceRU sar

on sat.companyID=sar.companyID

and sat.AttendanceTyID = sar.attendanceTyID

and sar.studentID = 169

and sar.termID is null

where sat.companyID= 1370

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

stax68,

Everything I used is in my big post. Add where sa.StudentID = 169 to the where clause and you will reduce the result set displayed in my post by 1 record, the one he wants to retain.

Lynn

stax68 SSChampion Points: 11711 More actions · Answer 13

I agree that that was the problem with the original code:

where sar.companyID= 1370 and sar.studentID = 169 and termID is null
But the versions posted fixed that problem. So your most recent suggestion is old news. But the problem is, the behaviour reportedly still hasn't gone away. That's why I asked for the code to be posted.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

I'm sorry, but I don't see how your code solves the problem. You still have a condition in the where clause checking for StudentID = 169. The table SchoolAttendanceTy does not have a StudentID field. Since there is no AttendenceTyID of 2 for StudentID = 169 and you do an outer join to SchoolAttendanceTy on AttendenceTyID, the StudentID field for SchoolAttendanceTyID = 2 is null, not 169. The condition StudentID = 169 will drop the record with the SchoolAttendanceTyID of 2 from the result set.

As I am found of saying, pretend I'm from Missouri and show me. I can't see any other reason for it to fail.

Lynn