mysql join 2 tables with equal field names

Scheduler Javascript
#1

Hi there
I have the following situation:
I would like to join two tables. Both tables have fields with the same name.
Ex: tables1.name / table2.name

render_sql: How is the correct syntax to have the name field of table1 as well the name field of table2?

thx in advance for your support

#2

Hello,

You can use alias:

$scheduler->render_sql("SELECT *, table1.name as name1, table2.name as name2 FROM ...","event_id","name1, name2");

Kind regards,
Ilya

#3

Hello Ilya
Thanks for your quick reply. If I use the alias notation I got the following error …

SELECT jos_jf_tasks.id,jos_jf_tasks.startdate AS sdate,jos_jf_tasks.duedate AS ddate,jos_jf_tasks.name AS tname,jos_jf_tasks.description,jos_jf_milestones.name AS mname FROM jos_jf_tasks JOIN jos_jf_milestones ON jos_jf_tasks.milestone=jos_jf_milestones.id WHERE sdate < ‘2011-10-01’ AND ddate > ‘2011-08-01’

!!!Uncaught Exception
Code: 0
Message: MySQL operation failed
Unknown column ‘sdate’ in ‘where clause’

I have no idea, where I am wrong …
Thanks in advance for your help …

#4

Hello,

Try following:

WHERE jos_jf_tasks.startdate < '2011-10-01' AND jos_jf_tasks.duedate > '2011-08-01'

Kind regards,
Ilya

#5

Hello Ilya
Thanks for your reply. But it doesn’t work. What I would like to have is the following SQL statement to populate the Scheduler:

select jos_jf_tasks.id,jos_jf_tasks.startdate,jos_jf_tasks.duedate,jos_jf_tasks.name,jos_jf_tasks.description,jos_jf_milestones.name from jos_jf_tasks join jos_jf_milestones on jos_jf_tasks.milestone = jos_jf_milestones.id;

How should I formulate it correctly in a render_sql statement?

Sorry for my beginner questions, but I am really lost …

Thanks again.

#6

Hello,

Sorry for the late response.
Please try following:

$scheduler->render_sql("SELECT *, ev.start_date as start_date1, ev_rec.start_date as start_date2 FROM  EVENTS_REC as ev_rec, EVENTS as ev", "event_id", "start_date, end_date, event_name, details, start_date1, start_date2");

Code itself doesn’t have much meaning but you can see how you can select two fields with the same name:

  1. Add aliases for tables
  2. Add aliases for fields

Kind regards,
Ilya