I have 2 tables one storing sizes the other company names. I would like to alter my existing query to give out different results from the table depending on the product type so at the moment I have and assigning the aliases accordingly
` SELECT
MAX(#__xyz_sizes.size) AS MAX,
MIN(#__xyz_sizes.size) AS MIN,
#__xyz_sizes.size_text,
#__xyz_companies.company_name
FROM
#__xyz_sizes
INNER JOIN #__xyz_companies ON #__xyz_companies.company_id = #__xyz_sizes.company_id
WHERE
#__xyz_companies.published = '1' AND
(92 >= #__xyz_sizes.bust_min and 92 <= #__xyz_sizes.bust_max
OR 73 >= #__xyz_sizes.waist_min and 73 <= #__xyz_sizes.waist_max
OR 97 >= #__xyz_sizes.hips_min and 97 <= #__xyz_sizes.hips_max)
GROUP BY company_name
`
What I need to happen is query to run assigning the
MAX(#__xyz_sizes.size) AS MAX,
MIN(#__xyz_sizes.size) AS MIN
To change to
MAX(#__xyz_sizes.size) AS MAXTOP,
MIN(#__xyz_sizes.size) AS MINTOP
WHERE
(92 >= #__xyz_sizes.bust_min and 92 <= #__xyz_sizes.bust_max
OR 73 >= #__xyz_sizes.waist_min and 73 <= #__xyz_sizes.waist_max)
and
MAX(#__xyz_sizes.size) AS MAXTROUSERS,
MIN(#__xyz_sizes.size) AS MINTROUSERS
WHERE
(73 >= #__xyz_sizes.waist_min and 73 <= #__xyz_sizes.waist_max
OR 97 >= #__xyz_sizes.hips_min and 97 <= #__xyz_sizes.hips_max)
and
MAX(#__xyz_sizes.size) AS MAXDRESS,
MIN(#__xyz_sizes.size) AS MINDRESS
(92 >= #__xyz_sizes.bust_min and 92 <= #__xyz_sizes.bust_max
OR 73 >= #__xyz_sizes.waist_min and 73 <= #__xyz_sizes.waist_max
OR 97 >= #__xyz_sizes.hips_min and 97 <= #__xyz_sizes.hips_max)
In other words when I echo out
$row->MAXTOP the query only looks at the bust and waist
$row->MAXTROUSERS the query only looks at the hips and waist
but dresses need all three ($row->MAXDRESS)
The queries all work separately fine but I need the query to run in one hit so that
I get
Retailer XXX
Dress size | Top Size | Trousers Size
When its displayed on the site using a foreach statement
The values that check the range (<=) are dymanic in the real code but just hard coded here for testing
Thanks