user3164151 user3164151 - 1 year ago 103
MySQL Question

Join query is going wrong

I have two tables. first is travellers_details and second is

traveller_details table has columns id, travel_mode, dep_from, arr_to,
dep_date, user_id and status.

user_info table has columns user_id, first_name, last_name, email.

I want all records of both table join with user_id

I wrote following query but not correct.

$sql="SELECT * FROM `traveller_details` FULL OUTER JOIN `user_info` on traveller_details.user_id=user_info.user_id where traveller_details.dep_from='".$this->from."' and traveller_details.arr_to='".$this->to."' and traveller_details.dep_date='".$this->sending_date."' and traveller_details.status='N'";

Answer Source

MySQL doesn't support full outer join, so your query definitely doesn't work. I suspect a left join is sufficient:

FROM `traveller_details` td LEFT OUTER JOIN
     `user_info` ui
     ON td.user_id = ui.user_id 
WHERE td.dep_from = '".$this->from."' AND
      td.arr_to = '".$this->to."' AND
      td.dep_date = '".$this->sending_date."' AND
      td.status = 'N'";

If all travelers have valid user info, then an INNER JOIN is sufficient.

Note that table aliases make the query easier to write and to read.