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'
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
Select * FROM promos p LEFT JOIN stores s
ON s.sid = p.category
WHERE p.category = {somevalue}
Here s.id stands for {column name} of STORE which is connecting to PROMOS table.