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.  
LVL 1
ncwAsked:
Who is Participating?
 
virmaiorConnect With a Mentor Commented:
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 AND products_groups.customers_group_id = 2
WHERE pd.products_id = products.products_id AND ptc.products_id = products.products_id
ORDER BY products_price;

I think that should do the trick... not entirely positive though, because you're trying to qualify the join not the result set.

(WHERE qualifies the result set and conditions in the ON clause qualify the join...
this is somewhat obscured because you use the short syntax for your joins..)
0
 
venkateshwarrCommented:
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;
0
 
ncwAuthor Commented:
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.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
ncwAuthor Commented:
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.
0
 
Renante EnteraSenior PHP DeveloperCommented:
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 :-)
0
 
nagkiCommented:
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;
0
 
ncwAuthor Commented:
virmajor: I'm pretty sure that's done got it thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.