[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

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.  
0
ncw
Asked:
ncw
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Renante EnteraCommented:
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
 
virmaiorCommented:
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
 
ncwAuthor Commented:
virmajor: I'm pretty sure that's done got it thanks.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now