how to get multiple specific values in one row with subqueries

[william67]

SELECT DISTINCT servicesid,short_name, long_name 
FROM tg_services 
WHERE servicesid IN 
(SELECT DISTINCT service_id FROM tg_srv_links 
WHERE service_id IN 
(SELECT DISTINCT service_id 
FROM tg_srv_links 
WHERE option_id = 9) AND option_id = 40 AND option_id = 27 AND option_id = 3) 
AND s_approved=1 
AND long_name <> '' 
ORDER BY `long_name` 
LIMIT 0, 5

The part I am having problems with is

option_id = 40 AND option_id = 27 AND option_id = 3

The tg_srv_links table looks like this

   row_id     |   service_id   |   option_id
-----------------------------------------------
      3       |      9         |    40
-----------------------------------------------
      4       |      9         |    27
-----------------------------------------------
      5       |      9         |    3
-----------------------------------------------
      6       |      6         |    40
-----------------------------------------------
      7       |      6         |    27
-----------------------------------------------
      8       |      6         |    22
-----------------------------------------------
      9       |      24        |    10
-----------------------------------------------
      10      |      24        |    27
-----------------------------------------------
      11      |      24        |    40
-----------------------------------------------
      12      |      24        |    3
 

What I need to do is find the specified service_id that has the exact 3 option_id's

In the table example above, the query should return 9 and 24 for the service_id's, since 9 and 24 both contain option_id's with 40, 27 and 3


anyone have any suggestions how to proceed?

[william67]

I tried the following but it doesn't work either, it return #1052 - Column 'option_id' in field list is ambiguous

SELECT DISTINCT servicesid, short_fr, name_fr
FROM tg_services
WHERE servicesid
IN (
 
SELECT DISTINCT service_id
FROM tg_srv_links
WHERE service_id
IN (
 
SELECT DISTINCT service_id
FROM tg_srv_links
WHERE option_id =9
)
AND option_id
IN (
 
SELECT DISTINCT option_id
FROM tg_srv_links AS L1
JOIN tg_srv_links AS L2 ON L1.service_id = L2.service_id
JOIN tg_srv_links AS L3 ON L1.service_id = L3.service_id
WHERE L1.option_id =3
AND L2.option_id =40
AND L3.option_id =27
)
)
LIMIT 0 , 30

Not sure if I am on the right path to a solution...

[william67]

I think I got it working properly as it returns results but I'll have to investigate each individual result and see if it's supposed to be there

here's what I ended up with

SELECT DISTINCT servicesid, short_en, name_en
FROM tg_services
WHERE servicesid
IN (
 
SELECT DISTINCT service_id
FROM tg_srv_links
WHERE service_id
IN (
 
SELECT DISTINCT service_id
FROM tg_srv_links
WHERE option_id =40
)
AND service_id
IN (
 
SELECT DISTINCT LA.service_id
FROM tg_srv_links AS LA
JOIN tg_srv_links AS L1 ON LA.service_id = L1.service_id
JOIN tg_srv_links AS L2 ON LA.service_id = L2.service_id
WHERE LA.option_id =10
AND L1.option_id =28
AND L2.option_id =33
)
)
LIMIT 0 , 30