Help Querying Data

Question

Post reply

Help Querying Data

Andy Watt

SSC Enthusiast

Points: 113
More actions
August 23, 2007 at 8:48 am

#373297

Hi,
I have a table consisting of two fields, both int, CompanyID and CategoryID
CompanyID, CategoryID
35764 49
35764 55
35764 107
35764 139
35764 141
35764 145
35764 149
35764 164
35764 175
35764 178
35765 49
35765 55
35765 135
35765 138
35765 139
35765 140
35765 141
I want to pass in a number of categoryid's to a stored procedure. eg '49, 55, 141' and return all companies that have all of them categories.
I cannot get it to work, I cannot use OR or IN as the company needs to be in all of the categories. If I use AND it will not work as the categoryid are on different rows, if that makes sense.
Any help would be great.
Thanks
Andy

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

See http://www.sommarskog.se and read about "passing arrays".

N 56°04'39.16"
E 12°55'05.25"

Andy Watt SSC Enthusiast Points: 113 More actions · Answer 2

Hi,

Thanks for the link but I cannot see an example of how to help me :-

take the following query :-

SELECT DICTINCT CompanyID FROM CompanyCategory WHERE CategoryID IN (49, 107)

This would return

35764

35765

Note that I don't want companyid 35765 as it doesn't have both category id's

I also cannot do this query

SELECT DICTINCT CompanyID FROM CompanyCategory WHERE CategoryID = 49 AND CategoryID = 106

as this would return 0 records.

I really only want to return companyid

35764

Anymore pointers.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

SELECT <columns> FROM CompanyCategory WHERE CategoryID in

(select companyid from CompanyCategory where categorid in (<all categories you want&gt having count(distinct categoryid)=<count of categories you want&gt

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Andy Watt SSC Enthusiast Points: 113 More actions · Answer 4

Thanks Matt,

I should have thought of that, with a few modifications it worked a treat.

Cheers

Andy

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

Would you care to post your solution, please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)