Simplify Inner Join

[dprichard]

I am starting to hand code all my queries instead of letting Navicat write them and was wondering if I could simplify this so I don't have to say t1. AND t2. in the where clause. If I take away the AND t2, I get the first matching result versus the one I want. Any help would be greatly appreciated.


SELECT t1.empto_emp_id, t1.empto_id, t1.empto_renewal_term, t1.empto_prev_carry_over, t1.empto_max_carry_over, t1.empto_time_accrued, t1.empto_waiting_period , CONCAT(e1.emp_fname, ' ', e1.emp_lname) AS emp_name, t3.to_type_name
FROM
emp_time_table AS t1 INNER JOIN employee AS e1 ON t1.empto_emp_id = e1.emp_id,
emp_time_table AS t2 INNER JOIN to_type AS t3 ON t2.empto_to_type_id = t3.to_type_id
WHERE t1.empto_id = '$empto_id' AND t2.empto_id = '$empto_id'

[Fumigator]

Yeah, that's a query joining 4 tables, using two different syntax methods, which is ill advised. Stick to one syntax or the other, don't mix em. And yeah it seems that you can remove the second join on emp_time_table and still get the same results.

[dprichard]

Fumigator. Thank you for the response. I thought I was only using 3 tables. emp_time_table, employee, to_types. Can you explain what you mean by two different syntax methods? Thank you!!!

[Fumigator]

There's this way to do a JOIN:


SELECT table1.col, table2.col
FROM table1, table2
WHERE table1.id = table2.id


Then there's this way:


SELECT table1.col, table2.col
FROM table1
JOIN table2
ON table1.id = table2.id


Both of those methods produce identical results.

Your query is using both syntax methods: the JOIN synax and the comma syntax. Yes there are only three tables involved, but the one table is referred to twice, which for all intents of the query, means there are 4 tables used. Two of those tables happen to point back to the same table.