SQL statement: Employees vs. Departments

[altmarvel]

I'm trying to use two tables in one query, but every time I try this statement, I get an error. Here's what I'm trying to accomplish:

In one query, I want to list the employee number, employee name (last name comma first name), their status (i.e. on leave, active, inactive), and, if active, what department they work in (which is indexed by code).

Here are my tables and examples of the data:
* A = active
* I = inactive
* L = on leave
(department code can only be blank if status is not A)

Employee:

Number | Name | Status | Department_Code
===========================================
0001 | Smith, John | A | AR
0002 | McMurray, Ken | I |
0003 | Jones, Brian | A | AS
0004 | Raymond, Cindy | L |
0005 | Cutter, Jim | A | AR

Departments:

Code | Department
===================
AR | Accounts receivable
AS | Assembly
MG | Upper management

I want to get a listing that looks like this:


0001 | Smith, John | A | AR | Accounts receivable
0002 | McMurray, Ken | I | |
0003 | Jones, Brian | A | AS | Assembly
0004 | Raymond, Cindy | L | |
0005 | Cutter, Jim | A | AR | Accounts receivable

... all in one SQL query. How is this done?

[RDM]

<?php
$sql = "SELECT T1.Number, T1.Name, T1.Status, T1.Department_Code,
T2.Department
FROM Employee AS T1,
Departments AS T2
WHERE T1.Department_Code=T2.Code";
?>


This will make it look like this:


0001 | Smith, John | A | AR | Accounts receivable
0003 | Jones, Brian | A | AS | Assembly
0005 | Cutter, Jim | A | AR | Accounts receivable

The other ones with the blank department code, will be lost. Or try adding: T1.Department_Code='' to the end of the WHERE clause.

If that still doesn't work, then add a blank Department_Code such as B = ' '

So then it will look like this:
Employee:

Number | Name | Status | Department_Code
===========================================
0001 | Smith, John | A | AR
0002 | McMurray, Ken | I | B
0003 | Jones, Brian | A | AS
0004 | Raymond, Cindy | L | B
0005 | Cutter, Jim | A | AR

Departments:

Code | Department
===================
AR | Accounts receivable
AS | Assembly
MG | Upper management
B |


0001 | Smith, John | A | AR | Accounts receivable
0002 | McMurray, Ken | I | B |
0003 | Jones, Brian | A | AS | Assembly
0004 | Raymond, Cindy | L | B |
0005 | Cutter, Jim | A | AR | Accounts receivable