August 14, 2009 at 2:01 pm
I need advice on the correct syntax of a statement to display master and transaction data. My problem is that I have a master table, and a transactions table, and need to display the master for all the records (records having or not having transactions corresponding to a master).
This is my test statement:
SELECT CoursesPerStudent.StudentName, Receipt.ReceiptQuantity
FROM CoursesPerStudent INNER JOIN
Receipt ON CoursesPerStudent.UniqueCourseID = Receipt.UniqueCourseID
WHERE (CoursesPerStudent.CourseStartedOn = '8/10/2009') AND (Receipt.TransactionType = '7')
My results from this statement displays only the master record with a corresponding transaction record, and I need to display all the records from the master table.
Please, Help!
August 14, 2009 at 2:03 pm
You need to change it to a Left Outer Join from an Inner Join, and you need to move the TransactionType into the join criteria instead of the Where clause.
Inner joins only return results where there are rows in both tables.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 14, 2009 at 2:04 pm
Deleted -- Gus's answer was more accurate.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply