I have three table in MySQL named as users, user_academic_info, department_info and I want to get a joining result on these three to get a search like option. The query is as below:
SELECT full_name, user_id, email, mobile, dept_name
FROM users, department_info
WHERE dept_id = (select dept_id from user_academic_info where ainfo_id = user_id)
AND full_name LIKE '%%'
OR user_id LIKE '%%'
OR email LIKE '%%'
OR mobile LIKE '%%'
OR dept_name LIKE '%%'
It shows me all the users with all the departments where as in database they are belong to only a single department.
see mysql join
SELECT * FROM `users` AS `u`
LEFT JOIN `user_academic_info` AS `ui` ON (
`u`.`id` = `ui`.`user_id`
)
LEFT JOIN `department_info` AS `di` ON (
`di`.`dept_id` =`ui`.`dept_id`
)
WHERE `u`.`full_name` LIKE '%%'
OR `u`.`id` = 4
The above query should get you all the fields from all 3 tables. Add your WHERE clause and prefix the fields with the correct table name.