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
