Why my Join does not work ?

Question

Why my Join does not work ?

vmrao

SSC Enthusiast

Points: 133
More actions
March 24, 2009 at 12:46 pm

#75441

My following query yields 0 records but I expect 3 records as 'catalogItems' table has 3 records for categoryId:1 and active:1.
SELECT catalogItems.itemId, catalogItems.itemDescription, itemDepartments.itemId AS itemIdInItemDept
FROM catalogItems LEFT OUTER JOIN
itemDepartments ON catalogItems.itemId = itemDepartments.itemId
WHERE (catalogItems.categoryId = 1) AND (catalogItems.active = 1) AND (itemDepartments.topParentOrg = '3654823')
ORDER BY catalogItems.itemDescription
Since I am using LEFT OUTER JOIN, I was expecting output such as follows (No matching records in itemDepartments table).
categoryId itemDescription itemIdInItemDept
11 desc1
12 desc2
13 desc3

Viewing 6 posts - 1 through 5 (of 5 total)

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

tosscrosby SSCoach Points: 18045 More actions · Answer 1

Just a hunch based on what your expected output is but category ID is 11, 12 and 13 not 1, Maybe where left(categoryId, 1) = 1 is what you're trying to do??? If I am way of base, please provide DDL of the tables and data for those tables and we'll give it anther shot.

-- You can't be late until you show up.

vmrao SSC Enthusiast Points: 133 More actions · Answer 2

My mistake. The column title is from the query, itemId (not categoryId).

Maxim Picard SSCrazy Points: 2052 More actions · Answer 3

vmrao (3/24/2009)
My following query yields 0 records but I expect 3 records as 'catalogItems' table has 3 records for categoryId:1 and active:1.
SELECT catalogItems.itemId, catalogItems.itemDescription, itemDepartments.itemId AS itemIdInItemDept
FROM catalogItems LEFT OUTER JOIN
itemDepartments ON catalogItems.itemId = itemDepartments.itemId
WHERE (catalogItems.categoryId = 1) AND (catalogItems.active = 1) AND (itemDepartments.topParentOrg = '3654823')
ORDER BY catalogItems.itemDescription
Since I am using LEFT OUTER JOIN, I was expecting output such as follows (No matching records in itemDepartments table).
categoryId itemDescription itemIdInItemDept
11 desc1
12 desc2
13 desc3

I believe the problem lies in your where condition:

AND (itemDepartments.topParentOrg = '3654823')

You said there are no matching records in itemDepartments in other words itemDepartments.topParentOrg = NULL.

You can apply that condition directly in your join (untested since no test data was provided...)

SELECT

catalogItems.itemId

,catalogItems.itemDescription

,itemDepartments.itemId AS itemIdInItemDept

FROM

catalogItems

LEFT OUTER JOIN itemDepartments

ON catalogItems.itemId = itemDepartments.itemId

AND (itemDepartments.topParentOrg = '3654823')

WHERE

(catalogItems.categoryId = 1)

AND (catalogItems.active = 1)

ORDER BY catalogItems.itemDescription

Let me know if that helps.

-- edit: reformatted code for clarity

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

Maxim is correct - as soon as you include a column from the outer table in the where clause - it is the same as using an inner join.

Move that check into the join clause for that table and you should get the results you are expecting.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

vmrao SSC Enthusiast Points: 133 More actions · Answer 5

Thanks, Maxim. It worked. I wrote sql joins before and I never encountered this issue with condition in where clause. As far as I remember, Left Join and Right Join worked fine with conditions in where clause and no matching records in one of the tables. It is driving me crazy why this case behaved so special or am I out of my mind ?