hi,
I'm not sure if this is an sql question or a combined sql/php question, so if i'm in the wrong question area, I apologize in advance.
I'm use to mssql and not sure how to exactly do this within mysql however... i have a number of tables, all with left joins to create individual sql queries
query 1:
SELECT
a.itemID
, b.typeID
, b.type
, c.subTypeID AS stid_1
, c.subType AS st1
, g.subTypeID AS stid_2
, g.subtype AS st2
, h.subTypeID AS stid_3
, h.subtype AS st3
, i.itemSizeID
, i.size
, d.pricingID AS p1
, d.price AS price1
, e.pricingID AS p2
, f.pricingID AS p3
, a.active
FROM
pim_items AS a
LEFT JOIN pim_types AS b
ON (a.typeID = b.typeID)
LEFT JOIN pim_subtype AS c
ON (a.subtypeID = c.subTypeID)
LEFT JOIN pim_subtype AS h
ON (a.subtypeID3 = h.subTypeID)
LEFT JOIN pim_subtype AS g
ON (a.subtypeID2 = g.subTypeID)
LEFT JOIN pim_sizes AS i
ON (a.sizeID = i.itemSizeID)
LEFT JOIN pim_pricing AS d
ON (a.price1 = d.pricingID)
LEFT JOIN pim_pricing AS e
ON (a.price2 = e.pricingID)
LEFT JOIN pim_pricing AS f
ON (a.price3 = f.pricingID);
which returns a result set of:
resultset 1
itemID typeID type stid_1 st1 stid_2 st2 stid_3 st3 itemSizeID size p1 price1 p2 p3 active
------ ------ ------- ------ -------- ------ -------- ------ ------ ---------- ------ ------ ------ ------ ------ ------
1 1 Service 1 Cut (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) 6 35.00 (NULL) (NULL) 1
2 1 Service 2 Blow Dry (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) 40 32.00 (NULL) (NULL) 1
3 1 Service (NULL) (NULL) 1 Cut (NULL) (NULL) 1 short 7 40.00 (NULL) (NULL) 1
4 1 Service (NULL) (NULL) 1 Cut (NULL) (NULL) 2 medium 8 45.00 (NULL) (NULL) 1
5 1 Service (NULL) (NULL) 1 Cut (NULL) (NULL) 3 long 10 55.00 (NULL) (NULL) 1
6 1 Service (NULL) (NULL) 2 Blow Dry (NULL) (NULL) 1 short 9 50.00 (NULL) (NULL) 1
7 1 Service (NULL) (NULL) 2 Blow Dry (NULL) (NULL) 2 medium 10 55.00 (NULL) (NULL) 1
8 1 Service (NULL) (NULL) 2 Blow Dry (NULL) (NULL) 3 long 11 60.00 (NULL) (NULL) 1
i have then created two queries from this one query based upon NULL values for stid_2 which returns the results of
query 2
SELECT
a.itemID
, b.typeID
, b.type
, c.subTypeID AS stid_1
, c.subtype AS serviceType
, d.pricingID AS p1
, d.price AS price1
, e.pricingID AS p2
, f.pricingID AS p3
, a.active
FROM
pim_items AS a
LEFT JOIN pim_types AS b
ON (a.typeID = b.typeID)
LEFT JOIN pim_subtype AS c
ON (a.subtypeID = c.subTypeID)
LEFT JOIN pim_pricing AS d
ON (a.price1 = d.pricingID)
LEFT JOIN pim_pricing AS e
ON (a.price2 = e.pricingID)
LEFT JOIN pim_pricing AS f
ON (a.price3 = f.pricingID)
WHERE c.subTypeID IS NOT NULL;
resultset 2
itemID typeID type stid_1 serviceType p1 price1 p2 p3 active
------ ------ ------- ------ ----------- ------ ------ ------ ------ ------
1 1 Service 1 Cut 6 35.00 (NULL) (NULL) 1
2 1 Service 2 Blow Dry 40 32.00 (NULL) (NULL) 1
and again - where the field stid_1 is NULL (with a param value for stid_2 = either 1 or 2 respectfully though not indicated within the example in 'query 3')
query 3
SELECT
a.itemID
, c.subTypeID AS stid_1
, c.subtype AS serviceType
, g.subTypeID AS stid_2
, g.subtype AS serviceType2
, h.itemSizeID AS sizeID
, h.size
, d.pricingID AS p1
, d.price AS price1
, e.pricingID AS p2
, f.pricingID AS p3
, a.active
FROM
pim_items AS a
LEFT JOIN pim_types AS b
ON (a.typeID = b.typeID)
LEFT JOIN pim_subtype AS c
ON (a.subtypeID = c.subTypeID)
LEFT JOIN pim_subtype AS g
ON (a.subtypeID2 = g.subTypeID)
LEFT JOIN pim_sizes AS h
ON (a.sizeID = h.itemSizeID)
LEFT JOIN pim_pricing AS d
ON (a.price1 = d.pricingID)
LEFT JOIN pim_pricing AS e
ON (a.price2 = e.pricingID)
LEFT JOIN pim_pricing AS f
ON (a.price3 = f.pricingID)
WHERE g.subTypeID IS NOT NULL
/*and g.subTypeID = '1'*/;
resultset 3
itemID stid_1 serviceType stid_2 serviceType2 sizeID size p1 price1 p2 p3 active
------ ------ ----------- ------ ------------ ------ ------ ------ ------ ------ ------ ------
3 (NULL) (NULL) 1 Cut 1 short 7 40.00 (NULL) (NULL) 1
4 (NULL) (NULL) 1 Cut 2 medium 8 45.00 (NULL) (NULL) 1
5 (NULL) (NULL) 1 Cut 3 long 10 55.00 (NULL) (NULL) 1
6 (NULL) (NULL) 2 Blow Dry 1 short 9 50.00 (NULL) (NULL) 1
7 (NULL) (NULL) 2 Blow Dry 2 medium 10 55.00 (NULL) (NULL) 1
8 (NULL) (NULL) 2 Blow Dry 3 long 11 60.00 (NULL) (NULL) 1
in mssql i can create these queries individually and then drag them into a larger query when dealing with cross-database joins/unions... for the sake of performance, and as I want to layout the grid in the format of
itemID serviceType serviceType2 Size Price Active ------ ----------- ------------ ---- ----- ------ 1 Cut (NULL) (NULL) (NULL) (NULL) 3 (NULL) (NULL) short 40.00 true 4 (NULL) (NULL) med 45.00 true 5 (NULL) (NULL) long 55.00 true
etc for 'blow dry'
hmmm, maybe this is an xml programming question, not a db question.... :\