August 27, 2012 at 3:44 pm
I've joined several tables in order to get data I need. However as I look @ data I realized there's a one to many relationship between product id and regions.
So, now I am attempting to filter records based on whether or not there's one or less than one regions associated to product that fall within specified status conditions (noted below).. I need help in forming proper syntax for accomplishing this task.
My query looks something like this...
this assumes only one table is being used but there's total of three..
select product, region, status
from products prd
inner join pd_status S on s.product_status_key=prd.status_key
where prd..product_status in ('active', 'on hold')
and sa.sa_status in ('canceled', 'On Hold')
and count(sa_status)<2.
I keep getting msg 147. I've tried with having, and as and contained in select as 'if' statement but still no luck. Any help is greatly appreciated.
August 27, 2012 at 4:03 pm
Take a look at HAVING. from your description i think that is what you are going to want.
http://msdn.microsoft.com/en-us/library/ms180199.aspx
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
August 27, 2012 at 4:04 pm
did you try something as
declare @region_assoc_prod int
set @region_assoc_prod = (select count(sa_status) from products)
select product, region, status
from products prd
inner join pd_status S on s.product_status_key=prd.status_key
where prd..product_status in ('active', 'on hold')
and sa.sa_status in ('canceled', 'On Hold')
and @region_assoc_prod<2.
or similar query?
Yes, read about having clause
August 28, 2012 at 7:28 am
This worked. Thank YOu.
August 28, 2012 at 7:59 am
Thank you. Very Helpful.
August 28, 2012 at 8:00 am
What worked? Lucas option doesn't seem like a great option.
You could use something like this for the HAVING
SELECT product, region, status
FROM products prd
INNER JOIN pd_status S on s.product_status_key=prd.status_key
WHERE prd.product_status in ('active', 'on hold')
AND s.sa_status in ('canceled', 'On Hold')
GROUP BY product, region, status
HAVING count(sa_status)<2.
However, I'm not sure it can work. You didn't post your DDL so we don't know if region is a column from Product or Pd_Status. It could have helped if you use complete names of the fields like you did on the where clauses.
For even better help, you could have posted sample data. That way we can have a better idea of what's needed. You might even need a LEFT JOIN or no JOIN at all, but is hard for us to know if we don't see what you see.
August 28, 2012 at 8:11 am
I tried declare and having clause both worked. My syntax for having which I tried yesterday was incorrect.
I am now verifying data results to ensure I am fetching products with only 1 region so I can now use query to build a report for my business partner. 🙂
Much appreciate the feedback. It's been years since I've played in data and code...am refreshing my skills again:-D
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply