SQL Left Outer Join Not doing what it should

[kimosabi]

I am trying to use information from two tables in a report. One table is item info and the other is the location for the item in the warehouse. The item may or may not show up in the location database but I always want to show it in the report. I am linking from the Item database to the location database and making it a Left Outer Join which should theoretically allow me to see all of the items and have locations only for the ones that show in the Location database. Sounds great but doesn't work for some reason. Are there any possible issues that I am forgetting?

 

[userr]

Hi,

If I understood you are making:

Select * From item, location Left outer join.....

So you are selecting all the items (table at the left), making the locations null where there is no location (and getting that nulls).

If this doesn´t work... Why? Which records does you get?

Sérgio Oliveira

 

[kimosabi]

I used the GUI Visual Linking but dragged it myself.

It is returning results consistant with an inner(equal) join.

 

[IdoMillet]

You are probably applying a selection criterion using some field from the Location table and therby removing all cases where the Left Outer Join returned an item will no location info.

Try to change that condition to
IsNUll({Location.field}) OR {Location.field}="your original condition".

Cheers,
- Ido ixm7@psu.edu

 

[nelsonl]

Using the Visual Linking GUI, it automatically defaults to a inner (=) join. Put your mouse cursor over the link between the two tables and right click on it. A window should pop up where one of the options is to define the type of join you wish to use.

HTH

Nelson

 

[kimosabi]

I actually figured out the issue when it comes to just two tables.

Thanks for your help!