Data Selection, Outer Join's

[leiela]

I have a table containing information on people, called person, this is linked to a second table called Employee, every person in Person has a record in Employee, so under most circumstances an inner join works well here.

However of those employee's "some" are managers, these have another table called management as well as thier employee and person's records, the management table links on the person table, using the same "personID"

What i need to do is pull back information on all "none" management employee's, and management the fall under certain criteria.

If i run a select statement on the "criteria" i need for managers, i lose all the none management employee's, because they don't even have a link to the manager table, they don't fill the criteria of the select statement.

I need a way of telling it to pull back the managers only if they meet the requirements, and bring back everyone who doesn’t have a link to this manager table.

im not sure if that was very clear ..

 

[lbass]

No not very clear. You need a left join from the employeees table to the management table. To return only non-managers, use a record selection like:

isnull({Managers.EmployeeID})

-LB

 

[leiela]

i wasn't sure it was clear, sorry.

heres the join's

{person.personid} = {employee.personid} (inner join)

{employee.folderid} = {manager.folderid} (left join as i want all employee's even ones without a manager record)

-------------

my selection formula is currently

{employee.FOLDERSTATUSFG} in ["B", "P"] and {manager.rank} = 'B'

however obvously this is "only" giving me employee's with a managers record, where their rank is = b.

what i want is a selection which will give me all managers with a rank of b.

AND

All employee's who do not have a record in the managers table and therefore no rank at all.

it's also worth noting that every employee has an {employee.folderid} as its the primary key for the table so i can't even ask it to bring back records where that is null.

 

[lbass]

What version of CR are you using? You should always identify this.

-LB

 

[leiela]

oh sorry its Crystal reports IX

 

[lbass]

You could use a command as your datasource, like this:

select employee.`field`, manager.`field`, person.`field`
from ((person inner join employee on
person.`personID` = employee.`personID`)
left outer join manager on
employee.`folderid` = manager.`folderid` and
manager.`rank` = 'B')
where employee.`FOLDERSTATUSFG` in ('B', 'P')

This will allow a select on the manager table and still maintain the left outer join. The punctuation will be specific to your datasource. Plug in the fields you want to display on your report.

-LB