Specifying selection criteria on a table with left outer join... 1

[ThirdLittlePig]

The problem is that when I specify criteria for a field in a table that has a left outer join, that join behaves like an an equal join.

Here’s the scenario: I have an entity for which there are several named contacts. Each contact can have multiple addresses which are assigned a ‘type’ (e.g. school, home, etc.). Not every contact position is filled for each entity. For example, each entity must have two leaders but some have three so the database accommodates the possibility of three leaders. In the report that I am preparing, I want to display a particular address type for the leaders but when I specify that the address type should be work, for example, using the Select Expert on the positions for which there are no assigned names (such as the optional third leader), the report only brings back records for which there is a third leader. So specifying this criteria makes the presence of a third leader mandatory. There are eight of these contacts for each entity and only three are consistently filled with the others sometimes being filled.

Can anyone help?

 

[Katy44]

Go to your selection criteria (I don't know what version you are using but for 9 or 10 it is in the Report menu) and change each piece of criteria to check for a null first. So for example

{thirdleader} = "John"

needs to be

(isNull({thirdleader}) or {thirdleader} = "John")

for each piece of criteria.

Does this work?

 

[ThirdLittlePig]

Nice one! Thank you - that seems to have done the trick for the vast majority of the records, though where the address type isn't possible, I still lose record.

e.g.
(isNull({thirdleaderaddresstype}) or {thirdleaderaddresstype} = "work")

Where there isn't a work address type for someone (say just a home address), I don't get the record. Would an "if then else" statement solve this or is there a more elegant solution to this problem?

 

[Katy44]

Can you provide more details of the structure of the tables and what it is you want to do exactly?

 

[ThirdLittlePig]

I'm trying to print a profile of the entity displaying all of the positions and their contact details (or in the case of positions that aren't filled, highlighting that fact) but want to make sure that the address details that we use are the ones displayed. The different address types are work, prime, and then one for the programme we administrate (it's this thrid one that I'm using).

There are three tables here: entity (links to person table for each leader 1 to 3), person (links to address table), address (contains multiple address types for each person).

My alternative is to go through those entities that are missing (they are few enough that this wouldn't be too onerous a task) and sort out the address details causing the problem but it would great to solve it without having to trawl through all of the data. [Feeling less stressed]

 

[Katy44]

I'm sorry I still don't understand. IS it when a person doesn't correspond with an entity that the problem occurs?

 

[ThirdLittlePig]

No, it's when the specified address type doesn't exist for a person that the entire record (entity and all people with their addresses) is omitted.

 

[lbass]

Use the left join, but don't put any criteria on the right hand table. Then create a formula to use for the address field:

if isnull({table.address}) or
{table.addresstype} <> "work" then
"" else {table.address}

This should return all people and only work addresses where they exist. You could also use a parameter for address type, if you wished.

-LB