Problem with LEFT JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with LEFT JOIN

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2006-07-18 : 10:50:59

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

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-18 : 11:51:16

Either change it to a right join or make it an inner join with a union. Just curious how are you joining on company_id when there's not company ID in the second tbl?

johnsql
Posting Yak Master

161 Posts

Posted - 2006-07-18 : 12:06:37

I forgot; there is a column named "CompanyID" for table "SchoolAttendanceTy" too like:
companyID AttendanceTyID Description
----------- -------------- --------------------------------------------------
1370 1 Present
1370 2 Excused Absence
1370 3 Unexcused Absence
1370 4 Tardy

(4 row(s) affected)

BTW, if I am supposed to use UNION, how can I do that? Thanks.

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-18 : 12:38:23

Did you try changing from a left join to right join? If not one of these should get you what you need.

select sar.AttendanceRuID, sar.StudentID, sar.AttendanceDate, sar.AttendanceTyID, sar.RollupCount, sat.AttendanceTyID, sat.Description
from SchoolAttendanceRU sar inner 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

union all

select companyid,'','','','',sat2.AttendanceTyID, sat2.Description
from AttendanceTyID sat2
where company_id = 1370
and not exists(select sat.AttendanceTyID
from SchoolAttendanceRU sar inner 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
and sat.companyID=sat2.companyID and
sat.AttendanceTyID = sat2.attendanceTyID)

or you could

select sar.AttendanceRuID, sar.StudentID, sar.AttendanceDate, sar.AttendanceTyID, sar.RollupCount, sat.AttendanceTyID, sat.Description
from SchoolAttendanceRU sar inner 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

union all

select companyid,'169','','','',AttendanceTyID,Description
from AttendanceTyID
where companyID= 1370
and AttendanceTyID = 2

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-07-18 : 12:51:02

You have the LEFT OUTER JOIN backwards. Also, never use RIGHT joins -- they are unintuitive and make your code harder to read.


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

I highlighted the TermID part -- where does that come from? Always prefix your columns with the table they come from.

It is good convention to alway select FROM your primary table and the do outer joins to auxillary tables. In this case, since you want to return every row for each Attendance Type, you select FROM that table and outer join to the Attendance. It is also good convention to list the non-null, primary columns from your primary table first in your SELECT followed by the columns that may be null after. This makes your output easier to read and understand.

Note that that criteria was moved to the JOIN since on a LEFT OUTER JOIN, if you put criteria on the columns in the outer table, that disallows NULLS and you then are stuck with an INNER JOIN. If that doesn't make sense, I suggest that you read more about and play with OUTER JOINS to really get the hang of them before continuing to work on your code.

- Jeff

johnsql
Posting Yak Master

161 Posts

Posted - 2006-07-18 : 13:50:29

jsmith8858
Can you explain more for me about WHY is it "miracle" to move "Note that that criteria was moved to the JOIN since on a LEFT OUTER JOIN, if you put criteria on the columns in the outer table, that disallows NULLS and you then are stuck with an INNER JOIN"???
The following query works for me as expected
select sat.AttendanceTyID, sat.Description, sar.AttendanceRuID, coalesce(sar.StudentID, 169) as studentID, sar.AttendanceDate, sar.AttendanceTyID,
sar.RollupCount, sar.termID
from SchoolAttendanceTy sat
left outer join SchoolAttendanceRU sar
on sat.companyID=sar.companyID and
sat.AttendanceTyID = sar.attendanceTyID and
sar.companyID= 1370 and sar.studentID = 169 and sar.termID is null
WHERE sat.CompanyID = 1370

and it gave me: ( 16 rows; one row is null...)
AttendanceTyID Description AttendanceRuID studentID AttendanceDate AttendanceTyID RollupCount termID
-------------- -------------------------------------------------- -------------- ----------- ------------------------------------------------------ -------------- ----------- -----------
1 Present 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
2 Excused Absence NULL 169 NULL NULL NULL NULL
3 Unexcused Absence 16408 169 2006-07-18 00:00:00.000 3 1 NULL
4 Tardy 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

(16 row(s) affected)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-07-18 : 14:02:46

quote:
Originally posted by johnsql

jsmith8858
Can you explain more for me about WHY is it "miracle" to move "Note that that criteria was moved to the JOIN since on a LEFT OUTER JOIN, if you put criteria on the columns in the outer table, that disallows NULLS and you then are stuck with an INNER JOIN"???
The following query works for me as expected
select sat.AttendanceTyID, sat.Description, sar.AttendanceRuID, coalesce(sar.StudentID, 169) as studentID, sar.AttendanceDate, sar.AttendanceTyID,
sar.RollupCount, sar.termID
from SchoolAttendanceTy sat
left outer join SchoolAttendanceRU sar
on sat.companyID=sar.companyID and
sat.AttendanceTyID = sar.attendanceTyID and
sar.companyID= 1370 and sar.studentID = 169 and sar.termID is null
WHERE sat.CompanyID = 1370

Of course it works as expected does -- your criteria is on the inner table.

I'm not sure about any miracles, but if you think logically about what a LEFT OUTER JOIN does, it should make sense.

If I say:

select A.ID, B.ID
from A left outer join B ....

Then I get all rows from A plus any matching rows (if any) from B. But what if there is no match in B? Then Nulls are returned whever you ask for one of B's columns. So, if you put criteria on one of B's columns, then any rows that are NULL will not match that criteria and the row will be filtered out. Since NULL values in B are now filtered out, only matching rows will be returned between A and B, which is the same as if you had used an INNER JOIN.

see: http://www.sqlteam.com/item.asp?ItemID=11122

for more info. And, as always, test it for yourself to find out using some sample data. try different combinations, see what happens. that's the best way to learn.

- Jeff

johnsql
Posting Yak Master

161 Posts

Posted - 2006-07-18 : 14:48:54

jsmith8858,
Thanks so much for your articles and explanations. Those help me too much at my work place because I have been stuck with the bug and nobody at my work place could give give me the firm explanation of putting criteria on JOIN clause instead on WHERE clause.
johnsql

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-07-18 : 15:41:17

Glad I could help ... lots of people have trouble with that aspect of outer joins.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources