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

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开发者_JAVA百科 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`)
) ... ;

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.

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

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;