如何从一个表中获取数据并在同一查询中使用这些值？

Question

I have two tables. And i want to get data from second table corresponding to value in one row from the first table.

I got two tables promos & stores

So what I'm trying to do is first pull ONE random row from PROMOS first and based on that row also get corresponding data from STORES.

the "category" column in PROMOS table = STORES (the name of second table)

promos.category = stores

---

promos has the following columns : id,image,category,storeid

stores is the second table that has columns: sid, storename, storeimage

Row in PROMOS table : 1, promopic.png, stores, 2

Row in STORES table : 2, shoppers, shoppers.png,

--

---

UPDATE: what I'm trying to do is the "stores" name is a row in promos. so the name of the second table i select data from is unknown until we get it from promos table first..

I've tried something like this

SELECT * FROM promos, promos.category WHERE promos.category.sid ='promos.storeid'

Answer 1

Try the following ;)

set @table_name = (SELECT category FROM promos where id=1); --change the 1 to whatever
set @sql =  CONCAT("select * from ",@table_name);
PREPARE query FROM @sql;
EXECUTE query;

The above assumes that you want to select everything from the table name we got from promos.category