473,326 Members | 2,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

LEFT JOIN out of whack

I'm having difficulties getting all rows to return from the left table
on a left join. For some reason, i only get the rows where there is a
match between the right and the left table. Here's what I have...

the tables:

student
-------
studentId (int)
lastname (varchar)
firstname (varchar)

class
-------
classId (int)
classDate (date)

absences
-------
studentId (int)
classI (int)
madeUp (tinyint)

year
-------
yearId (int)
yearDesc (varchar)

class_year
----------
classId (int)
yearId (int)

the query:

SELECT
COUNT(a.studentId) as absent_days,
SUM(a.madeup) as days_made_up,
s.studentId
FROM
student as s
LEFT JOIN absences as a ON s.studentId = a.studentId
INNER JOIN class as c ON a.classId = c.classId
INNER JOIN class_year as cy ON c.classId = cy.classId
INNER JOIN year as y ON cy.yearId = y.yearId
WHERE c.classDate BETWEEN '2004-01-01' AND '2004-01-31'
AND y.yearId = 4
GROUP BY s.studentId
ORDER BY s.lastname, s.firstname

The problem:
Ten of the students had 0 absences during that month. However, they
aren't showing up on the query. For whatever reason, the left join only
returns student Id's where there's a corresponding entry in the absences
table. What I want is for all the students' Id's to show up with just a
"null" or "0" value for the "days made up" and "absent days" parts of
the query.

Can someone help me?

Thanks,

Jon
Jul 19 '05 #1
0 1154

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Marek Lewczuk | last post by:
Hello, I have a strange problem, maybe some of you will be able to explain me something. I use LEFT JOIN as a substitute for subselects. It's true that many subselects can be rewriten using LEFT...
0
by: Petre Agenbag | last post by:
Hi List Me again. I'm trying to return from multiple tables, the records that have field "information_sent" between two dates. The tables are all related by means of the id of the entry in the...
0
by: Soefara | last post by:
Dear Sirs, I am experiencing strange results when trying to optimize a LEFT JOIN on 3 tables using MySQL. Given 3 tables A, B, C such as the following: create table A ( uniqueId int not...
0
by: Jon Trelfa | last post by:
I'm having difficulties getting all rows to return from the left table on a left join. For some reason, i only get the rows where there is a match between the right and the left table. Here's...
1
by: Paul Bramscher | last post by:
Here's one for pathological SQL programmers. I've got a table of things called elements. They're components, sort of like amino acids, which come together to form complex web pages -- as nodes...
4
by: jbm05 | last post by:
Hi, I'm curious about the computational complexity of a query I have. The query contains multiple nested self left joins, starting with a simple select, then doing a self left join with the...
3
by: Ian Boyd | last post by:
i know nothing about DB2, but i'm sure this must be possible. i'm trying to get a client to create a view (which it turns out is called a "Logical" in DB2). The query needs a LEFT OUTER JOIN, but...
2
by: tricard | last post by:
Good day all, I have a large outer joined query that I want to have some criteria. The select query is gathering all part numbers from tblPartNumbers, left joining to tblPartNumberVendor (since...
1
by: naveenchhibber | last post by:
Hi all pls tell me that the following statment is valid in oracle 9i or 10g.. update ws set received_by_facility = coalesce(rbf_ouk.organizational_unit_id, 0), ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.