Select query for an optional join on tables
Posted on 2006-06-01
I've got a four tables: items, users, courses, and universities
Items belong to users and users are optionally associated with a single course and/or uni. If a user has a uni and a course, the following query works fine and returns all the details for a user if they have a value for their uni and course attributes...
SELECT users.*, courses.course_name, universities.uni_name FROM items, users, courses, universities WHERE items.item_id = $id AND users.user_id = items.user_id AND courses.course_id = users.user_course AND universities.uni_id = users.user_uni
If a user doesn't have a uni or course though, the query doesn't return any of the user's details... How can I get around this. I basically need to somehow implement the logic:
If users.user_uni <> 0 then get the uni name and...
if users.user_course <> 0 then get the course name
I'm running MySQL 4.1.18.