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

create routines

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.