I've made several attempts to get the outcome needed, but can't get my mind around it for some reason.
Table Setup:
estimate_parts - This table has a column called 'ItemRefListID','Quantity'.
parts - This table has columns 'group_id' , 'ListID' , 'id', 'our_code' , 'phase_id'.
groups_parts - This table has columns 'group_id' , 'part_id','Quantity' , 'phase_id'.
groups - This table has one column 'id'.
Relationships:
estimate_parts.ItemRefListID = parts.ListID
groups_parts.group_id = parts.group_id (each 'group' has many different parts)
groups_parts.part_id = parts.id (each 'part' can belong to many groups)
So basically every line in estimate_parts has a ItemRefListID, And every ItemRefListID is tied directly to only one line (via ListID) in 'parts'.
That one 'part' either refers to a GROUP of parts, or to just itself.
If the field 'group_id' is null (or 0), the part is not a group.
If the field 'group_id' is not null, then the part is a group.
When the 'part' is NOT a group, we need to show it's 'our_code' , 'phase_id' , 'ListID' , and 'Quantity' (from estimate_parts).
When the 'part IS a group, then we need to only show it's 'ListID', but for every part in the group (matching group_id's) we need to show the 'our_code', 'Quantity', and 'phase_id'.
And it needs to be flat in one table. In other words, as you look down the resulting table you should not be able to tell which parts were originally in a group, and which parts were straight from 'estimate_parts'. Well, I guess you could if you look closely at the resulting 'ListID's because they would be similar per group.
Would anyone care to help me figure this out? Here's a starting SQL statement that returns just parts in groups. I can't figure out where to put the IF or IFNULL to detect if it's a group though, and if not, to just return the part details. I've tried various places but none of them really work.
SELECT * FROM `estimate_parts` AS ep
LEFT OUTER JOIN (
SELECT p.ListID, gp.group_id, gp.p_id, gp.our_code
FROM parts AS p
INNER JOIN (
SELECT gp.group_id, p.id AS p_id, p.our_code
FROM groups_parts AS gp
LEFT OUTER JOIN parts AS p ON p.id = gp.part_id
) AS gp ON p.group_id = gp.group_id
) AS p ON p.ListID = ep.ItemRefListID
Thanks a ton!
Ahh, a weekend of rest does wonders for a stressed mind. Here is my working SQL statement. Posted here for reference for anyone else who runs across a similiar problem.
SELECT ep.ItemRefListID,
IF(p.group_id, gp.our_code, p.our_code) AS our_code,
IF(p.group_id, gp.id, p.id) AS part_id,
IF(p.group_id, gp.Quantity, ep.Quantity) AS Quantity
FROM `estimate_parts` AS ep
LEFT OUTER JOIN parts AS p ON p.ListID = ep.ItemRefListID
LEFT OUTER JOIN (
SELECT p_parent.ListID, p_sub.our_code, p_sub.id, p_sub.Quantity
FROM groups AS g
LEFT OUTER JOIN (
SELECT gps.group_id, p.our_code, p.id, gps.qty*u.qty AS Quantity
FROM groups_parts AS gps
INNER JOIN parts AS p ON p.id = gps.part_id
LEFT OUTER JOIN units AS u ON u.id = gps.unit_id
WHERE gps.phase_id = 1
) AS p_sub ON p_sub.group_id = g.id
LEFT OUTER JOIN parts AS p_parent ON p_parent.group_id = g.id
) AS gp ON gp.ListID = ep.ItemRefListID
1 to 2 of 2