See comments below... -- Arul <[EMAIL PROTECTED]> wrote: > Hi > > I Dont think this will work coz I need the company > which has serviceid = 1 > AND serviceid = 2 and serviceid = 3 > I think that you are mixing natural language with logical expression, right? The expression above always returns FALSE, that is, no record will be returned. > 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.
This company won't be retrieve because just services 1. Look the query again Arul, we are filtering the records: serviceid = 1 or serviceid = 2 or serviceid = 3 Only records with serviceid 1, 2, or 3 will be returned in the query. Then we are grouping to count the records for each group, and finally (with the HAVING COUNT), we are making sure that only company with the three services are shown. Have you really tried this query? Because I tried and it worked unless I misunderstood what you want to do. > 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. > The count for this company will be 3 because the same reason as above. Look this simple table: id1,id2 1,1 1,2 1,3 2,3 3,4 3,5 3,6 Select * from tblService where id2 = 1 or id2 = 2 or id2 = 3 will return: id1,id2 1,1 1,2 1,3 2,3 Select id1, count(*) from tblService where id2 = 1 or id2 = 2 or id2 = 3 group by id1 returns: id1,count(*) 1,3 2,1 and finally Select id1, count(*) from tblService where id2 = 1 or id2 = 2 or id2 = 3 group by id1 having count(*) = 3 id1,count(*) 1,3 Only and only id1 (company) 1 services the three service type. Let me know if this is not what you wanted. Bye and Good Luck. > 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 > __________________________________________________ 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
