Let’s say I am querying/joining two tables, each with certain identical field names:
SELECT * FROM sales1 s1 JOIN sales2 s2 USING (id)
Is it possible to prefix the columns for each table with the tablename (or some other prefix), so for example instead of getting data such as:
id id product product etc.
It looks like:
s1_id s2_id s1_product s2_product etc.
Without doing it manually for every single field?
Advertisement
Answer
In MySQL you can do:
EXPLAIN SELECT * FROM sales1 s1 JOIN sales2 s2 USING (id);
Followed by:
SHOW WARNINGS;
That will show you a rewritten version of the original query with quoted, fully qualified column and table names like so:
/* select#1 */ select `dbname`.`s1`.`id` AS `id`,`dbname`.`s1`.`product` AS `product`,`dbname`.`s2`.`product` AS `product` from `dbname`.`sales1` `s1` join `dbname`.`sales2` `s2` where (`dbname`.`s2`.`id` = `dbname`.`s1`.`id`)
You then need to do some regex find-replace afterwards. So yes, do it manually.