Link to home
Start Free TrialLog in
Avatar of jm_jackson
jm_jackson

asked on

Select query for an optional join on tables

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.

Many thanks,

JJ
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jm_jackson
jm_jackson

ASKER

thanks thats perfect