Passing parent query column value into subquery

Question

I'm building a query that performs to fetch users prize data. I'm trying to get all prize data in one column via group_concat function.

I've almost done with the query, but one thing I'm unable to do is to pass the parent query user id value into subquery to fetch data accordingly.

+----+---------------+------------+------------------------------+
|id  |user_email     |user_name   | prizes                       |
+----+---------------+------------+------------------------------+
|1   |user_email     |user_name   | user1 prizes                 |
|2   |user_email     |user_name   | user2 prizes                 |
|3   |user_email     |user_name   | user3 prizes                 |
|4   |user_email     |user_name   | user4 prizes                 |
|5   |user_email     |user_name   | user5 prizes                 |
|6   |user_email     |user_name   | user6 prizes                 |
|7   |user_email     |user_name   | user7 prizes                 |
|8   |user_email     |user_name   | user8 prizes                 |
+----+---------------+------------+------------------------------+

Here is the code:

SELECT u.ID,u.user_email,u.user_name,
(SELECT GROUP_CONCAT( concatval ORDER BY id DESC SEPARATOR  '|~prize~|~sprt~|' )

FROM (

SELECT s.user_id,s.id, CONCAT_WS('|~value~|~sprt~|', s.id,s.offer, IFNULL(p.post_title,'N/A'), s.time, IFNULL(s.expiry_date,'N/A'), IFNULL(m.meta_value,'N/A'), IFNULL(a.meta_value,'N/A'),s.coupon) as concatval
FROM s_data s
LEFT JOIN sc_posts p ON s.campaign_id = p.ID
LEFT JOIN sc_postmeta m ON s.campaign_id = m.post_id AND m.meta_key = 'phone'
LEFT JOIN sc_postmeta a ON s.campaign_id = a.post_id AND a.meta_key = 'address'
WHERE s.user_id = 2 AND s.status = 'awarded' AND (STR_TO_DATE(s.expiry_date,'%M %d,%Y') > DATE(NOW()) OR s.expiry_date IS NULL OR s.expiry_date = '' )
ORDER BY s.id DESC LIMIT 0,7

    ) tmptable
GROUP BY user_id

) as prizes

FROM sc_users u

GROUP BY u.ID

I need to pass the u.id of 1st line to line 11 where it says(WHERE s.user_id = 2) to fetch data accordingly. I tried to use this, but it says "Unknown column 'u.ID' in 'where clause'". Can someone help me please. Thanks in advance.

no its not possible your inner query is executed first and then the top query will get the data with the select as you have it now. — Abhik Chakraborty

StanislavL StanislavL · Accepted Answer · 2014-05-06T07:29:28

Ypu can add the subquery in the FROM section and JOIN with users Like this

SELECT u.ID,u.user_email,u.user_name,
 prizes.concatval

FROM sc_users u 
  JOIN (SELECT GROUP_CONCAT( concatval ORDER BY id DESC SEPARATOR  '|~prize~|~sprt~|' )

FROM (

SELECT s.user_id,s.id, CONCAT_WS('|~value~|~sprt~|', s.id,s.offer, IFNULL(p.post_title,'N/A'), s.time, IFNULL(s.expiry_date,'N/A'), IFNULL(m.meta_value,'N/A'), IFNULL(a.meta_value,'N/A'),s.coupon) as concatval
FROM s_data s
LEFT JOIN sc_posts p ON s.campaign_id = p.ID
LEFT JOIN sc_postmeta m ON s.campaign_id = m.post_id AND m.meta_key = 'phone'
LEFT JOIN sc_postmeta a ON s.campaign_id = a.post_id AND a.meta_key = 'address'
WHERE s.user_id = 2 AND s.status = 'awarded' AND (STR_TO_DATE(s.expiry_date,'%M %d,%Y') > DATE(NOW()) OR s.expiry_date IS NULL OR s.expiry_date = '' )
ORDER BY s.id DESC LIMIT 0,7

    ) tmptable
GROUP BY user_id

) prizes on.prizes.user_id=u.ID

GROUP BY u.ID

Passing parent query column value into subquery

1 Answers