mySql内部/左边从变量表加入

I have a table called activities, inside that table i have these useful columns (not mentioning all the columns)

`item_id` AND `table`

And few different tables, now basically what this activities table does is to point to the targets inside different tables, say

SELECT * FROM `activities` WHERE `user_id` = 1;

You get :

ID:1    TABLE:photos    ITEM_ID:55
ID:2    TABLE:posts     ITEM_ID:57

and so on...

I want to get the data from the value of the table column, here I have one photos one posts these are different tables.

I could do this using PHP loop:

$query = 'SELECT * FROM `activities` WHERE `user_id` = 1;'

$results = $DB->GetRows($query);
foreach($results as $result){
    $table = $result->table;
    $query2 = "SELECT * FROM `$table` WHERE `user_id` = '1'";
    $res = $DB->GetRows($query2);

}

So here I can get the results form the other tables, but I want a query instead of multiple queries, if that’s possible.

You can use the UNION keyword in order to merge the results of two results into one, but this is only possible if they share the same structure.

Also, It is risky business to SELECT from a variable that contains your table name, I would strongly suggest you to change it for security purposes.

You can get results in several resultsets:

SELECT  photos.*
FROM    activities
JOIN    photos
ON      photos.id = activities.item_id
WHERE   activities.table = 'photos'

SELECT  posts.*
FROM    activities
JOIN    posts
ON      posts.id = activities.item_id
WHERE   activities.table = 'posts'

Most probably, table setups for photos and posts are different, so you can't get them in a single resultset.

Not possible with standard SQL. Consider this:

create table foo (bar text);
insert into foo ('baz');
select * from (select bar from foo)

There is absolutely NO way for you to tell the DB that you actually want it to use the baz value you've stored in the foo table as the name of a table to do its select * on. Instead, you'll actually get:

ERROR 1248 (42000): Every derived table must have its own alias

because that select bar query's results are going to be used a temporary table, and you'll fetch all of that temp table's records instead, ending up with a result set of baz, instead of fetching from a table named baz.

You need to use a stored procedure, dynamically built your query, and exec() it within the database.