Possible? SQL Left Outer Join with where clause for the right table

[beltmanjr]

Hi there,
I got this difficult issue having two table where I wish to filter on the right table in a left outer join and have all rows return from the left and only those from the right that match, and if they dont match a NULL value is ok.

Department Table
DepartmentID DepartmentName
31 Sales
33 Engineering
34 Clerical
35 Marketing

Employee Table
LastName DepartmentID
Rafferty 31
Jones 33
Steinberg 33
Robinson 34
Smith 34
Jasper 36


A standard left outer join gives:
SELECT *
FROM employee E
LEFT OUTER JOIN department D
ON employee.DepartmentID = department.DepartmentID

E.LastName E.DepartmentID D.DepartmentName D.DepartmentID
Jones 33 Engineering 33
Rafferty 31 Sales 31
Robinson 34 Clerical 34
Smith 34 Clerical 34
Jasper 36 NULL NULL
Steinberg 33 Engineering 33


Trying to now implement a where clause to only get those employees in DepartmentID 33, but I still wish to list those that are not in that department, but only their information from the employee table. The result I hope to achieve:

E.LastName E.DepartmentID D.DepartmentName D.DepartmentID
Jones 33 Engineering 33
Rafferty 31 NULL NULL
Robinson 34 NULL NULL
Smith 34 NULL NULL
Jasper 36 NULL NULL
Steinberg 33 Engineering 33

Anyone know how to fix this?

 

[beltmanjr]

I should add that eventually I wish for the end result to be pulled into crystal reports (XI). At current I can't even get the sql working directly on the oracle db server...
So a solution that is crystal focussed is extra welcome

 

[goranm]

Hi!

Maybe this can help.

It´s another syntax than you use, but I think it will work.

If you get it to work you can create a command in Crystal, and paste the query there.

select
E.LastName,
nvl(E.DepartmentID,NULL),
nvl(D.DepartmentName,NULL),
nvl(D.DepartmentID,NULL)
from
employee E,
department D
where
E.DepartmentID = D.DepartmentID(+) and
D.DepartmentID(+) = '33'

/Goran

 

[beltmanjr]

Cheers for that, I'll certainly try,
but how would I (or rather where) copy this command into crystal?

Is this in the database expert and get it in as a new table?

 

[goranm]

Hi!
Command objects are listed as Add Command under your data source, just above where all the tables are listed.

/Goran

 

[beltmanjr]

Great and thanks.
I actually found the best manner in which to handle the issue is using a sub query like:

SELECT *
FROM employee E
LEFT OUTER JOIN
(SELECT * FROM department D
WHERE DepartmentID = 33) TempDepTable
ON employee.DepartmentID = TempDepTable.DepartmentID

I found this to be working great.
However I discovered that there was a 1 to many relationship between my left and right table, so a
SELECT DISTINCT * statement has to replace one or both SELECT * statements,

but I am afraid to discover tomorrow that although I might have multiple foreign key occurences in the right table, there might be unique values to those rows in the right table which I guess may result in something where I see multiple times the same value for the left table with different ones from the right.. bugger!

And with that I fear I encountered a shortcoming of the database

 

[IdoMillet]

You can try:

SELECT *  
FROM employee E
  LEFT OUTER JOIN department D
       ON E.DepartmentID = D.DepartmentID 
       AND 
       D.DepartmentID = 33

or

SELECT *  
FROM employee E
  LEFT OUTER JOIN department D
       ON E.DepartmentID = D.DepartmentID 
       WHERE 
       IsNull(D.DepartmentID) OR D.DepartmentID = 33

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[beltmanjr]

Hi Ido,
well, solution 1 wouldn't give any results for unmatched rows left table and breaks the idea of an left outer join

Solution 2 is clever, but as I discovered doesn't work either. This is again because unmatched rows from the left aren't displayed.

This is because you only select the rows from the right table where the departmentId = 33 or departmentId is null whilst the idea was to display unmatched rows from the left using the is null statement.

It seems that a where statement breaks the unmatched rows idea of an outer join.

By using a sub query you first identify the rows from the right you wish to select. This is then stored in a temp table. Next you join using the left outer join. This looks at your temp table only and the outer join isn't broken by the where clause on the right because as far as the sql statement is aware there is no where clause in the main query to consider.

So this fixes the problem....
My last issue still remains, but is impossible to resolve. If the foreign keys I'm joining on in the right table can not uniquely identify a row I'll simply have to accept that more rows from the right that match on that foreign (composite) key will be returned.

The only solution is to humanly interfere and fix the rows so that they will be unique when matched on that (composite) foreign key.

Many thanks for your thought!

 

[lbass]

You could consider using a union query like:

SELECT LastName, DepartmentID, Null, Null
FROM employee E
union
SELECT LastName, DepartmentID, DepartmentName, DepartmentID
FROM employee E
LEFT OUTER JOIN department D
where DepartmentID = 33

-LB

 

[smcnulty2000]

You might also consider this:

SELECT 
e.*,

(select max(d.DepartmentID ) from department d where e.DepartmentID = d.DepartmentID
and d.DepartmentID='33') DeptID, 

(select max(d.DepartmentName ) from department d where e.DepartmentID = d.DepartmentID
and d.DepartmentID='33') DeptName

FROM   employee E

Which is just another approach, but seems to apply to your situation adequately. To get more entries you just do a cut and paste and change the field you are seeking and the output column alias.

This allows the E table to spit out every entry, then it only reports the values from D that are relevant leaving nulls in their place.

The use of the max is to avoid an error message if you have more than one entry on the right that are department 33.

One reason I could see a set of dups on the right is if an employee were to switch departments, so you might want to look for some date fields to weed out the dates that are no longer valid.

Also, you could keep your left outer join in its more raw form and do the 'dept 33' stuff inside of crystal. There's no absolute requirement that the SQL be the only approach to this. In that case you just bring in everything and use a suppression formula on your Dept and Dept Name.

 

[RustyAfro]

Beltmanjr,

Ido's solution 2 does work, as I use it all the time against an Oracle 9i server.

It allows you to avoid using a command and let crystal build the SQL.

In the Row Selection in Crystal putting:

IsNull(D.DepartmentID) OR D.DepartmentID = 33

Will build a SQL string that brings in unmatched rows on the left OR DeptId's that are equal to 33.

If you reverse the order:
D.DepartmentID = 33 OR IsNull(D.DepartmentID)

Then you may get only matches on both sides (at least mine do).

If you can avoid a command, you should.

 

[beltmanjr]

Cheers RustyAfro.
I have now created a view in oracle itself that fixed the issue.