Outer joins make my head hurt...double outer joins doubly so. Also, it's 2am and I'm far from a SQL ninja, but here goes:
SELECT jctCustomerFoodgroup.int_CustomerID, lkpFoodgroup.int_FoodgroupID, lkpFoodgroup.str_FoodgroupHandle, tblProduct.int_ProductID, tblProduct.str_ProductName FROM ( lkpFoodgroup LEFT OUTER JOIN jctCustomerFoodgroup ON lkpFoodgroup.int_FoodgroupID = jctCustomerFoodgroup.int_FoodgroupID ) LEFT OUTER JOIN tblProduct ON jctCustomerFoodgroup.int_ProductID = tblProduct.int_ProductID WHERE jctCustomerFoodgroup.int_CustomerID = ?
Untested (I don't have my lab running at the moment) and the exact syntax is going to vary by database vendor (in particular some DBs want the parentheses, some don't).
Also, for rows without a product the int_CustomerID is going to return NULL as well, since everything is logically an outer join of lkpFoodgroup and there's no matching rows in jctCustomerFoodgroup to pull it from. You could fake it with something like this in its place:
Nevermind, I see you got a better answer already. ;-)
Although I'm not sure what the cross join does... I thought a cross join was basically just an inner join without a where clause to limit it? -Which is why I've never found a practical use for a cross join.
Cross joins have very little use -- I think we found a practical use a few months ago where we were mapping multiple items to multiple entries. Other than that, it's good if you make a product that's sizes xx to yy with 10 colors and all 10 colors in xx and yy. And that's about it.
Comments 4
SELECT
jctCustomerFoodgroup.int_CustomerID,
lkpFoodgroup.int_FoodgroupID,
lkpFoodgroup.str_FoodgroupHandle,
tblProduct.int_ProductID,
tblProduct.str_ProductName
FROM
(
lkpFoodgroup LEFT OUTER JOIN jctCustomerFoodgroup
ON lkpFoodgroup.int_FoodgroupID = jctCustomerFoodgroup.int_FoodgroupID
)
LEFT OUTER JOIN tblProduct
ON jctCustomerFoodgroup.int_ProductID = tblProduct.int_ProductID
WHERE
jctCustomerFoodgroup.int_CustomerID = ?
Untested (I don't have my lab running at the moment) and the exact syntax is going to vary by database vendor (in particular some DBs want the parentheses, some don't).
Also, for rows without a product the int_CustomerID is going to return NULL as well, since everything is logically an outer join of lkpFoodgroup and there's no matching rows in jctCustomerFoodgroup to pull it from. You could fake it with something like this in its place:
SELECT ( ... )
Reply
Although I'm not sure what the cross join does... I thought a cross join was basically just an inner join without a where clause to limit it? -Which is why I've never found a practical use for a cross join.
Reply
Reply
Reply
Leave a comment