Hi I Dont think this will work coz I need the company which has serviceid = 1 AND serviceid = 2 and serviceid = 3
So let's take this Query , Select companyid, count(*) from CompanyServices where serviceid = 1 or serviceid = 2 or serviceid = 3 group by companyid having count(*) = 3 If i have another company say companyid = 5 ,who has serviceid = 1 AND serviceid = 6 AND serviceid = 7 , even this company will be retrieved which will be against the search criteria. Also another company whose companyid = 7 has serviceid = 1 AND serviceid = 2 AND serviceid = 3 AND serviceid = 5 .This company will not be retrieved since the count is not 3.But by search criteria , this company should be retrieved. So how do we handle these situations -Arul ----- Original Message ----- From: "Francisco Reinaldo" <[EMAIL PROTECTED]> To: "Arul" <[EMAIL PROTECTED]>; "MySQL" <[EMAIL PROTECTED]> Cc: "Arul" <[EMAIL PROTECTED]> Sent: Wednesday, July 17, 2002 3:27 AM Subject: Re: help me with this query > Hi, > > Select companyid, count(*) from CompanyServices where > serviceid = 1 or serviceid = 2 or serviceid = 3 > group by companyid > having count(*) = 3 > > Bye and Good Luck. > --- Arul <[EMAIL PROTECTED]> wrote: > > Hi All > > > > The Table design goes like this > > > > Company Table > > ---------------- > > companyid integer > > name varchar(100) > > > > > > Services Table > > ---------------- > > ServiceID integer > > Service varchar(50) > > > > > > Company Services > > ------------------- > > companyid integer - References > > Company(companyid) > > serviceid integer - References > > Services(ServiceID) > > > > The Values in Company table are > > > > companyid name > > 1 'ABC' > > 2 'XYZ' > > 3 'CDE' > > > > > > The Values in Services table are > > > > serviceid service > > 1 'Ebiz' > > 2 'MainFrames' > > 3 'CAD' > > 4 'Maintenance' > > > > The Values in Company Services table are > > > > companyid serviceid > > 1 1 > > 1 2 > > 1 3 > > 2 1 > > 2 2 > > 3 4 > > > > > > I have a List Box where the user selects his Need > > for Services. > > Suppose the User selects 1 , 2 and 3 , then i need > > to show him the company > > which provides him all 1 , 2 and 3 services . By our > > data , its company 1 > > I cant search by using IN coz it would select a > > record even if the company > > is providing any one of the services. > > I did this by using self join for ex: > > > > select > > distinct(c.companyid) , c.name > > from > > company c , companyservices cs1, > > companyservices cs2, > > companyservices cs3 > > where > > cs1.serviceid = 1 > > AND cs2.serviceid = 2 > > AND cs3.serviceid = 3 > > AND c.companyid = cs1.companyid > > > > > > > > Is there any other way , i could achive the result > > without using a self join > > > > -Arul > > > > sql , query > > > > > > > > > > > --------------------------------------------------------------------- > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list > > archive) > > > > To request this thread, e-mail > > <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: > > http://lists.mysql.com/php/unsubscribe.php > > > > > __________________________________________________ > Do You Yahoo!? > Yahoo! Autos - Get free new car price quotes > http://autos.yahoo.com > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
