CODEWITHSUNDEEP
mysql
Slevin
Slevin

Reputation: 4222

How to use column from outer select inside subquery

I'm trying to use the value of a column called children_ids inside a subquery:

SELECT hotels.id,
       hotels.hotel,
       hotels.country,
       hotels.corrected,
       hotels.children_type,
       hotels.children_ids as children_ids,
       (SELECT SUM(trx_input.count) FROM hotels LEFT JOIN trx_input ON hotels.trx_id = trx_input.id WHERE hotels.id IN (children_ids)) as count_children,
       trx_input.count
FROM hotels
LEFT JOIN trx_input ON hotels.trx_id = trx_input.id
WHERE hotels.country = 'DE' AND children_type != 2
ORDER BY hotels.hotel
LIMIT 1000

But count_children is always NULL. If I replace children_ids with some real values, it works:

SELECT hotels.id,
       hotels.hotel,
       hotels.country,
       hotels.corrected,
       hotels.children_type,
       hotels.children_ids as children_ids,
       (SELECT SUM(trx_input.count) FROM hotels LEFT JOIN trx_input ON hotels.trx_id = trx_input.id WHERE hotels.id IN (338666,338665,338456,338691)) as count_children,
       trx_input.count
FROM hotels
LEFT JOIN trx_input ON hotels.trx_id = trx_input.id
WHERE hotels.country = 'DE' AND children_type != 2
ORDER BY hotels.hotel
LIMIT 1000

Is there any way to use the value of an outer column inside a subquery?

Upvotes: 0

Views: 49

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

You can do what you want using find_in_set():

   (SELECT SUM(i.count)
    FROM hotels h2 JOIN
         trx_input i
         ON h2.trx_id = i.id
    WHERE find_in_set(h2.id, h.children_ids) > 0
   ) as count_children,

However, you should fix your data structure to use a proper junction table. Storing lists of ids in a comma delimited column is not the right way to store data.

Upvotes: 1

Gouda Elalfy
Gouda Elalfy

Reputation: 7023

You can replace In (children_ids) by In ( select children_ids from hotels hotls1 where hotels.id = hotels1.I'd)

Upvotes: 0

Related Questions