Select from multiple tables with results labeled by table-CodePudding

Is there any method for doing a MySQL multi-table select that can tag/label the columns from the table the column originated from?

table1
tID   name    desc
---------------------
1    foo     lots of foo
2    bar     lots of bar
3    foobar  none of these

table2
oID   tID    orderNo
------------------------
1    1       19001
2    1       19002
3    3       19004
4    2       19005

SELECT t1.*, t2.* FROM table1 t1,table2 t2 WHERE t1.tID=t2.tID ;

The output would be:

tID    name    desc            oID      orderNo
-----------------------------------------------
1      foo     lots of foo      1       19001
1      foo     lots of foo      1       19002
2      foo     lots of bar      4       19005
3      foo     none of these    3       19004

But I am looking for something that would output like the following:

t1.tID    t1.name    t1.desc            t2.oID      t2.orderNo
---------------------------------------------------------------
1         foo        lots of foo        1           19001
1         foo        lots of foo        1           19002
2         foo        lots of bar        4           19005
3         foo        none of these      3           19004

This small example represents a bigger issue I am trying to tackle. It would allow for one query but also be able to tell me which table the data came from when parsing the data.

CodePudding user response：

You can use aliases for each column and I suggest writing a proper join.

SELECT t1.tID as `t1.tID`,   
       t1.name as `t1.name`,   
       t1.desc as  `t1.desc`,  
       t2.oID  as `t2.oID`, 
       t2.orderNo  as `t2.orderNo`
FROM table1 t1
INNER JOIN table2 on  t1.tID=t2.tID ;