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
