MYSQL: subquery return as table name in join

Question

Is it possible to use subquery return as a table name in join?

Dynamic Table(dynamictable)

+--------+--------------+----------+-----------+--------+
| tableid| tablename    | settings | pack_type | status |
+--------+--------------+----------+-----------+--------+
|     24 | xxxxxx       | NULL     | F         | A      |
|     25 | YYYYYY       | NULL     | M         | A      |
|     30 | ZZZZZZ       | NULL     | M         | A      |
|     26 | AAAAAA       | NULL     | M         | A      |
+--------+--------------+----------+-----------+--------+

Product Table(products)

+--------------+------------+
| tableid      | product_id |
+--------------+------------+
|           30 |          1 |
|           30 |          2 |
|           25 |          3 |
|           30 |          4 |
+--------------+------------+

xxxxxx

+------------+--------------+
| product_id | product_cost |
+------------+--------------+
|          1 |          350 |
|          2 |          200 |
|          4 |          200 |
+------------+--------------+

i.e(for example):

select * 
  from products as p 
  left join (select tablename 
               from dynamictable as d 
              where d.tableid = p.tableid) as dt 
    on dt.product_id = p.product_id;

Yes....have you tried it..? but you forget to include dt.product_id in return — Dipesh Parmar
– Dipesh Parmar, Commented Aug 27, 2013 at 7:48
@dipesh: Yes i tried .. it throws mysql error unknown column 'p.tableid' and if i put any directly value means instead of 'p.tableid' & the error shows unknown column 'dt.product_id'. — Pradeep shyam
– Pradeep shyam, Commented Aug 27, 2013 at 7:55
in the subquery p.tableid will through error as because there is no alias for p inside subquery — Manigandan Arjunan
– Manigandan Arjunan, Commented Aug 28, 2013 at 4:00

Dipesh Parmar · Accepted Answer · 2013-08-27 07:57:58Z

1

You need to use LEFT JOIN into subquery.

select * 
  from products as p 
  left join (select tablename,products.tableid 
               from dynamictable as d
               left join products on d.tableid = products.tableid
            ) as dt 
    on dt.product_id = p.product_id;

answered Aug 27, 2013 at 7:57

Dipesh Parmar

27.3k8 gold badges65 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL: subquery return as table name in join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related