asked on

Sql Join filter

With the following sql:

SELECT pd.products_name,
IF (products_groups.customers_group_price IS NULL, products.products_price, products_groups.customers_group_price) AS products_price
FROM products_description pd, products_to_categories ptc, products
LEFT JOIN products_groups ON products.products_id = products_groups.products_id
WHERE pd.products_id = products.products_id AND ptc.products_id = products.products_id
ORDER BY products_price;

How can I filter the join so that 'products_groups.customers_group_price' is only selected for the condition in the if statement BUT also only where 'products_groups.customers_group_id = 2'?

I've put '2' to simplifier the question, where as actually it will be a variable.

venkateshwarr

try this...

SELECT pd.products_name,
IF (products_groups.customers_group_price IS NULL, products.products_price, products_groups.customers_group_price) AS products_price
FROM products_description pd, products_to_categories ptc, products
LEFT JOIN products_groups ON products.products_id = products_groups.products_id
WHERE pd.products_id = products.products_id AND ptc.products_id = products.products_id AND
products_groups.customers_group_id = 2
ORDER BY products_price;

ncw

ASKER

Yes I'd tried that but it then only show records where products_groups.customers_group_id = 2, whereas I want it to show all records and only replace the products_price with customers_group_price if customers_group_price is not null (working so far with the above) and only if customers_group_id = 2.

ncw

ASKER

I''ve tried using:
IF ( ( products_groups.customers_group_price IS NULL AND products_groups.customers_group_id <> 2 ) AS products_price
but if products appear with multiple group_id's, which they will, then will the number of records be increased due to multiple matches on products.products_id = products_groups.products_id rather than being limited to unique products.products_id's?

It's the properties of the join that really specifying to filter out product_group records by customers_group_id.

Renante Entera

Hi ncw!

I think this is what you are looking for :

SELECT pd.products_name, products_groups.customers_group_price AS products_price
FROM products_description pd, products_to_categories ptc, products
LEFT JOIN products_groups ON products.products_id = products_groups.products_id
WHERE pd.products_id = products.products_id AND ptc.products_id = products.products_id
AND products_groups.customers_group_price IS NOT NULL
ORDER BY products_price;

Hope this helps you. Just try it.

Goodluck!
eNTRANCE2002 :-)

nagki

try this:

SELECT pd.products_name,
IF (products_groups.customers_group_price IS NULL AND products_groups.customers_group_id !=2, products.products_price, products_groups.customers_group_price) AS products_price
FROM products_description pd, products_to_categories ptc, products
LEFT JOIN products_groups ON products.products_id = products_groups.products_id
WHERE pd.products_id = products.products_id AND ptc.products_id = products.products_id
ORDER BY products_price;

ASKER CERTIFIED SOLUTION

virmaior

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ncw

ASKER

virmajor: I'm pretty sure that's done got it thanks.