[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Select query for an optional join on tables

Posted on 2006-06-01
Medium Priority
Last Modified: 2006-11-18
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,

Question by:jm_jackson
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 400 total points
ID: 16807042
SELECT users.*, courses.course_name, universities.uni_name
FROM items
JOIN users
  ON items.item_id = $id AND users.user_id = items.user_id
LEFT JOIN courses
  ON courses.course_id = users.user_course
LEFT JOIN universities
  ON universities.uni_id = users.user_uni

Author Comment

ID: 16807090
thanks thats perfect

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month18 days, 22 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question