What is the best way to get all data if there are the same field names in other tables?

Question

I have the following db.

If I select * and join all the tables, desc and active will be mixed up.

Now I can write all like omc_courses.desc, omc_trainer.desc etc, but if I have many field, it is not practical.

So I am thinking if I can write like select *, omc_courses.desc AS course_desc, omc_trainer.desc AS trainer_desc, etc for fields which has the same name.

Or is there any way you can suggest?

Thanks in advance.

CREATE TABLE IF NOT EXISTS `omc_courses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  ...
  `desc` varchar(255) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  ...
  PRIMARY KEY (`id`)
) ... ;



CREATE TABLE IF NOT EXISTS `omc_trainer` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  ...
  `desc` varchar(255) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
   ...
  PRIMARY KEY (`id`)
) ... ;


CREATE TABLE IF NOT EXISTS `omc_anothertable` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  ...
  `desc` varchar(255) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
   ...
  PRIMARY KEY (`id`)
) ... ;

Answer 1

The best way ofcourse is as you have suggested to use aliases to remove ambiguity between same column names from different tables.

I do not think there is any other way to avoid this ambiguity of same column names as far as I know.

eg: with aliases -

select omc_c.*, omc_c.desc AS course_desc, omc_t.desc AS trainer_desc
from omc_courses as omc_c inner join omc_trainer as omc_t

Answer 2

If the field name is ambiguous, using tablename.fieldname is the only way to go that I know of.

You could create short aliases for the table names:

... FROM omc_courses AS c

and then address the field names through that alias:

select *, c.desc AS course_desc, t.desc AS trainer_desc,  ....

but I think that's the best one can do in terms of abbreviations.

Answer 3

use column aliases as suggested but i'd probably create a view as follows so i dont have to think about it again:

drop view if exists omc_courses_view;

create view omc_courses_view as
select
 c.id as course_id,
 c.active as course_active,
 t.id as trainer_id,
 t.active as trainer_active,
 ...
from
 omc_courses c
inner join omc_trainer t on c.id = t.course_id
inner join ...

select * from omc_courses_view;