0

I am trying to do a query with an outer join, and I've always used Oracle Pl/SQL in the past, but am now trying to learn MySQL. I'm trying to join 3 tables, USERS, USER_TYPE, and GRADE. The USERS table contains column, USER_TYPE_ID, which is a foreign key to USER_TYPE, and it also contains column, GRADE_ID which is a foreign key to GRADE. I need the outer join because user types could be student, faculty, etc, and if it is a faculty member then the USER does not have a grade, hence a NULL GRADE_ID in the USER table. Here is what my query would have looked like in the past using (+) for the outer join.

SELECT A.USER_NAME
     , A.USER_TYPE_ID
     , B.USER_TYPE_DESC
     , A.GRADE_ID
     , C.GRADE_DESC
  FROM USERS A
     , USER_TYPE B
     , GRADE C
 WHERE A.USER_TYPE_ID = B.USER_TYPE_ID
   AND A.GRADE_ID = C.GRADE_ID (+);

Could someone please help me translate this into a MySQL query?

Thanks in advance!

Ryan
  • 617
  • 2
  • 7
  • 16

2 Answers2

1
SELECT u.user_name, u.user_type_id, ut.user_type_desc, u.grade_id, g.grade_desc
FROM users u JOIN user_type ut ON (ut.user_type_id = u.user_type_id)
LEFT JOIN grade g ON (u.grade_id = g.grade_id);
RC.
  • 27,409
  • 9
  • 73
  • 93
  • Thanks for the input RC., but that query still only grabs users that have a grade (Students), and throws out all faculty and administrators because they are not in a grade and therefor their GRADE_ID is NULL. I need the query to grab all users, even if they do not have a GRADE_ID. – Ryan Jul 19 '12 at 01:27
  • Thanks alot RC, worked like a charm. That'll be a good example for future reference as I continue with MySQL. – Ryan Jul 19 '12 at 02:14
0
SELECT u.user_name, u.user_type_id, ut.user_type_desc, u.grade_id, g.grade_desc
FROM users u, user_type ut
LEFT OUTER JOIN grade g ON u.grade_id = g.grade_id
WHERE ut.user_type_id = u.user_type_id
Belcap
  • 141
  • 1
  • 3