Select query for an optional join on tables

Posted on 2006-06-01
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 142

    Accepted Solution

    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

    thanks thats perfect

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now