views:

42

answers:

2

I have 2 tables. For simplicities sake 'u' has the following columns
userid
divisionid

'd' has the following:
divisionid
name

I did not create this table, otherwise I would not have this problem. u.DIVISION can be NULL. d.DIVISION cannot.

Running the following creates the appropriate data, but it also filters out every single userid that has NULL for it's divisionid. Is there anyway to still show all the userid's regardless of their divisionid and if the divisionid is not null, to then display the name of the division?

"SELECT userid, d.NAME
FROM u,d
WHERE u.divisionid = d.divisionid
ORDER BY userid"

+1  A: 

Use an outer join:

SELECT userid, d.NAME
FROM u
LEFT OUTER JOIN division d
ON u.divisionid = d.divisionid
ORDER BY userid
dcp
A: 

Using the Oracle or implied join syntax implies an INNER JOIN. An inner join eliminates records that don't meet the criteria.

An explicit join using the JOIN clause allows you to specify the type of join.

A LEFT OUTER JOIN keeps all rows in the first table, regardless if there are matching rows in the second table.

SELECT userid, d.NAME
FROM u
LEFT OUTER JOIN division d
ON d.divisionid = u.divisionid
ORDER BY userid
Marcus Adams