Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Selecting records based on count Expand / Collapse
Author
Message
Posted Monday, August 27, 2012 3:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 7, 2012 2:26 PM
Points: 8, Visits: 30
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.

Post #1350628
Posted Monday, August 27, 2012 4:03 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 20, 2014 7:16 AM
Points: 945, Visits: 1,769
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 faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1350633
Posted Monday, August 27, 2012 4:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 19, 2013 5:05 PM
Points: 3, Visits: 34
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

Post #1350634
Posted Tuesday, August 28, 2012 7:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 7, 2012 2:26 PM
Points: 8, Visits: 30
This worked. Thank YOu.
Post #1350917
Posted Tuesday, August 28, 2012 7:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 7, 2012 2:26 PM
Points: 8, Visits: 30
Thank you. Very Helpful.
Post #1350959
Posted Tuesday, August 28, 2012 8:00 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:27 AM
Points: 3,342, Visits: 7,226
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.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1350962
Posted Tuesday, August 28, 2012 8:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 7, 2012 2:26 PM
Points: 8, Visits: 30
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
Post #1350971
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse