Toad World® Forums

Heads up on Outer Join syntax difference between Access, SAS EG, and TDA


#1

Scenario: Two tables, PARENT table has a foreign key, and a Lookup table which contains the associated primary key. You want to see every record in the PARENT table even if there is no matching value in the Lookup table, i.e. an Outer join.

Outer Join in Access: “Include all records from ‘PARENT’ and only those records from ‘Lookup’ where the joined fields are equal.”

Outer Join in SAS Enterprise Guide: “All rows from the PARENT table”

Outer Join in TDA: “Select all rows from Lookup

Is this by design or changed in a later version of TDA? I am using 2.5.0.913 currently.


#2

Right click on the join line and you will see some options. See screenshot.

Debbie
JoinOptions.png


#3

It’s easier to see with the Screenshot. To see All CONTACTS including those which don’t have an Address:

In TDA: choose “Select all rows from Address

In Access which has a similar graphic interface, you would select:
“Choose all records from Contact and only those records from Address where the joined fields are equal”

In SAS EG you would select: “All rows from Contact table”

The Heads Up part, is that if you are used to working in Access or SAS Enterprise Guide, one almost automatically chooses “Select all rows from Contact” to create an outer join, this is incorrect and won’t return all Contact rows in TDA.

This isn’t a bug, but it is something to be aware of when using TDA because it can cause errors which are difficult to catch.


#4

I think this is a good point as user do expect consistancy across products. I entered Cr74,757 to look into this.

Thanks

Debbie