如何在sql中使用select ... union ...时检测结果来自哪个表

Question

    $result = mysql_query( "SELECT id FROM table1 UNION ALL SELECT id FROM table2;" , $link);

    while($end = mysql_fetch_assoc($result))
    {
    echo $end['id']; // how separate and specify output is frow which table ?   
    }

i have two table (phpmyadmin), and i want to search in both of them with 1qeury, then i'm use UNION to combine, now i want to seprating output and specify from what table is evry result (row)? tnx

Answer 1

Add an extra column that indicates which table it is:

SELECT "table1" which, id FROM table1
UNION ALL
SELECT "table2" which, id FROM table2

Now you can use $end['which'] to know which table each row came from.

which isn't a column in the table itself. It's an alias for the literal string that's just in the query.

To get the results ordered alternating between the tables, you can do:

SELECT *
FROM (
    SELECT "table1" which, id FROM table1
    UNION ALL
    SELECT "table2" which, id FROM table2
) x
ORDER BY id, which