CODEWITHSUNDEEP
mysqlselect
hpaknia
hpaknia

Reputation: 3118

How can I rename all columns of a table at select?

I have two tables with 21 identical column name, but different data. I want to join the tables, but access both columns (row["phase1"], row["other_phase1"]): How can I rename/alias all of them in the select statement with a small statement like:

SELECT t_process.*,t_task_process.* AS other_column WHERE ...

Should I go through writing AS statement for all 21 columns or there's a concise way? If not is there no way to get results as an array with these keys: t_process.phase1 , .... , t_task_process.phase1 , ... ?
Also I can not rename them in the Data Base.
Ther original query is :

SELECT t_process.*,t_task_process.* 
FROM t_process,t_task_process 
WHERE t_process.id = t_task_process.id
AND (t_process.phase1<>t_task_process.phase1
OR t_process.phase2<>t_task_process.phase2
OR t_process.phase3<>t_task_process.phase3
OR t_process.phase4<>t_task_process.phase4
OR t_process.phase5<>t_task_process.phase5
OR t_process.phase6<>t_task_process.phase6
OR t_process.phase7<>t_task_process.phase7
OR t_process.phase8<>t_task_process.phase8
OR t_process.phase9<>t_task_process.phase9
OR t_process.phase19<>t_task_process.phase10
OR t_process.phase11<>t_task_process.phase11
OR t_process.phase12<>t_task_process.phase12
OR t_process.phase13<>t_task_process.phase13
OR t_process.phase14<>t_task_process.phase14
OR t_process.phase15<>t_task_process.phase15
OR t_process.phase16<>t_task_process.phase16
OR t_process.phase17<>t_task_process.phase17
OR t_process.phase18<>t_task_process.phase18
OR t_process.phase19<>t_task_process.phase19
OR t_process.phase20<>t_task_process.phase20    
OR t_process.phase21<>t_task_process.phase21)

There is no force for mysql to have what I want. I just want to know If there is a concise way or not.

Upvotes: 0

Views: 975

Answers (1)

peterm
peterm

Reputation: 92845

You can leave * for all columns in the first table and explicitly specify aliases for columns in table2

SELECT t1.*,
       t2.phase1 phase1_2,
       t2.phase2 phase2_2,
       t2.phase3 phase3_2,
       ...
  FROM t_process t1 JOIN t_task_process t2 
    ON t1.phase1 <> t2.phase1
    OR t1.phase2 <> t2.phase2
    OR t1.phase3 <> t2.phase3
    ...

SQLFiddle

On a side note: use standard JOIN syntax.

Upvotes: 1

Related Questions