CODEWITHSUNDEEP
mysql
joels
joels

Reputation: 7739

How to concat table names to columns in mysql

Lets say I'm joining two tables in mysql

Owners - id, name
Pets - id, name, owner_id
SELECT pets.*, owners.* FROM pets JOIN owners ON owners.id=pets.owner_id

The results come back with multiple id and name columns. Is there a way to get the columns to be returned in a way that distinguishes the tables like pets.id and owners.id without specifying every column name?

Upvotes: 0

Views: 2252

Answers (2)

joels
joels

Reputation: 7739

Table:

CREATE TABLE `my_table_name` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The query:

SELECT GROUP_CONCAT(CONCAT(" ", table_name,".", column_name, " as `", table_name, ".", column_name, "`")) FROM information_schema.columns WHERE table_name='my_table_name';

Result:

 my_table_name.id as `my_table_name.id`, my_table_name.name as `my_table_name.name`

Upvotes: 1

DS501
DS501

Reputation: 133

Just name the columns?

SELECT pets.id AS pets_id,
   owners.id AS owners_id,
   pets.name AS pets_name,
   owners.name AS owners_name
FROM pets JOIN owners
ON owners.id=pets.owner_id

Upvotes: 0

Related Questions