MySQL Forums
Forum List  »  General

LEFT JOIN - loosing a line from LEFT table selection
Posted by: Libor Dub
Date: April 22, 2014 07:27AM

I have a trouble with following query:

SELECT
IFNULL(t4.ItemID,
IFNULL(t3.ItemID, IFNULL(t2.ItemID, t1.ItemID))) AS ID,
IFNULL(t4.ParentItemID,
IFNULL(t3.ParentItemID,
IFNULL(t2.ParentItemID, t1.ParentItemID))) as Parent,
IFNULL(tp4.ItemNo, IFNULL(tp3.ItemNo, IFNULL(tp2.ItemNo, tp1.ItemNo))) AS ItemNo
FROM ItemsParents AS t1
LEFT JOIN ItemsDB AS tP1
ON t1.ItemID = tP1.ID
LEFT JOIN ItemsParents AS t2
ON t1.ItemID = t2.ParentItemID
LEFT JOIN ItemsDB AS tP2
ON t2.ItemID = tP2.ID
LEFT JOIN ItemsParents AS t3
ON t2.ItemID = t3.ParentItemID
LEFT JOIN ItemsDB AS tP3
ON t3.ItemID = tP3.ID
LEFT JOIN ItemsParents AS t4
ON t3.ItemID = t4.ParentItemID
LEFT JOIN ItemsDB AS tP4
ON t4.ItemID = tP4.ID
WHERE t1.ParentItemID =
(
SELECT ID
FROM ItemsDB
WHERE ItemNoInt = 359
)
This table should return two rows (822 and 875) from the first part of selection and append more rows from following ItemsParents LEFT JOINS. It appends the new rows, but one row from the original two gets lost.
822
859
834
846
810
...so the row 875 got lost.
If I remove or incapacitate the LEFT JOIN from parent items (by "= 1 -- " - commenting out the original condition):

SELECT IFNULL
(
t4.ItemID,
IFNULL(t3.ItemID, IFNULL(t2.ItemID, t1.ItemID))) AS ID,
IFNULL(t4.ParentItemID,
IFNULL(t3.ParentItemID,
IFNULL(t2.ParentItemID, t1.ParentItemID))) as Parent,
IFNULL(tp4.ItemNo,
IFNULL(tp3.ItemNo, IFNULL(tp2.ItemNo, tp1.ItemNo))) AS ItemNo
FROM ItemsParents AS t1
LEFT JOIN ItemsDB AS tP1
ON t1.ItemID = tP1.ID
LEFT JOIN ItemsParents AS t2
ON t1.ItemID = 1 -- t2.ParentItemID
LEFT JOIN ItemsDB AS tP2
ON t2.ItemID = tP2.ID
LEFT JOIN ItemsParents AS t3
ON t2.ItemID = 1 -- t3.ParentItemID
LEFT JOIN ItemsDB AS tP3
ON t3.ItemID = tP3.ID
LEFT JOIN ItemsParents AS t4 -- t4.ParentItemID
ON t3.ItemID = 1
LEFT JOIN ItemsDB AS tP4
ON t4.ItemID = tP4.ID
WHERE t1.ParentItemID =
(
SELECT ID
FROM ItemsDB
WHERE ItemNoInt = 359
)
...it returns correct two rows from left joined table (822 and 875).

I googled cases of lost rows in left joined table, but none of them seems to apply to this case. Any idea?

Regards,

Libor

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.