Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Selecting records based on count


Selecting records based on count

Author
Message
Candls
Candls
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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.
CapnHector
CapnHector
SSC Eights!
SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)

Group: General Forum Members
Points: 917 Visits: 1789
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
lucas_leon88
lucas_leon88
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
Candls
Candls
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 30
This worked. Thank YOu.
Candls
Candls
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 30
Thank you. Very Helpful.
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8488 Visits: 18096
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.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Candls
Candls
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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:-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search