How do I include table names in a MySQL query?

1
votes

This seems like it would be a simple setting, but I cannot find it. I do inner join queries on tables that have similar column names. It would be nice to include the table name in the query results, so the people receiving the data can differentiate more easily. For example:

Table1: id name timestamp

Table2: id name timestamp table1_id

Table3: id name timestamp table2_id

Then I tie it all together with a query:

select * from table1 
inner join table2 on table1.id=table2.table1_id 
inner join on table2.id=table3.table2_id;

The results have similar column header names:

id name timestamp id name timestamp table1_id id name timestamp table2_id

It's hard to tell the data apart. Of course the example query is short and silly and pointless. If I do an actual query with all the data it get more complicated. Couldn't the column header name include the table name?

table1.id table1.name table1.timestamp table2.id table2.name table2.timestamp table2.table1_id table3.id table3.name table3.timestamp table3.table2_id

4
mysql

4 Answers

1
votes

Instead of writing 

select * from

you can write 

select table1.id as table1_id,

and do the same for the other columns so that the results set would show you the names you give yourself for each column

1
votes

You can use aliases to identify columns:

SELECT table1.id AS table1_id FROM ...

But you would have to do this for each field you want to select.

1
votes

You have ambiguous column names in output: table1.id, table2.id

Adding alias for columns should solve this:

SELECT table1.id as t1_id, table2.id as t2_id
-1
votes

try this.hope it will help you.

    SELECT table1.id as t1_id, table2.table1_id as t2_id
    FROM tablename 
    inner join table2 on table1.id=table2.table1_id 
    inner join table3 on table2.id=table3.table2_id;