how to get multiple specific values in one row with subqueries

william67

Expand|Select|Wrap|Line Numbers

 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

Expand|Select|Wrap|Line Numbers

option_id = 40 AND option_id = 27 AND option_id = 3

The tg_srv_links table looks like this

Expand|Select|Wrap|Line Numbers

 
   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

Expand|Select|Wrap|Line Numbers

 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

Expand|Select|Wrap|Line Numbers

 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

how to get multiple specific values in one row with subqueries

re: how to get multiple specific values in one row with subqueries

re: how to get multiple specific values in one row with subqueries

Similar MySQL Database bytes

/bytes/development

/bytes/it

/bytes/about