Left Outer Join Not Working Correctly

[prevost999]

I have a report that uses 6 tables, and has 1
left outer join. The report returns records as if
it were an equal join, and is not resulting in all desired records. I am using CR 8 and SQL Server
via ODBC. Any help is appreciated. Thank you.

The statment is:
SELECT
ttdsls040001."t_orno", ttdsls040001."t_cuno", ttdsls040001."t_odat", ttdsls040001."t_refa",
ttdsls041001."t_cuno", ttdsls041001."t_rdat", ttdsls041001."t_seqn_i",
ttiitm001001."t_item", ttiitm001001."t_dsca",
ttdsls045001."t_ddat",
ttttxt010001."t_text"
FROM
{ oj (((("mkdaily"."dbo"."ttdsls040001" ttdsls040001 INNER JOIN "mkdaily"."dbo"."ttdsls041001" ttdsls041001 ON
ttdsls040001."t_orno" = ttdsls041001."t_orno&quot
INNER JOIN "mkdaily"."dbo"."ttiitm001001" ttiitm001001 ON
ttdsls041001."t_item" = ttiitm001001."t_item&quot
INNER JOIN "mkdaily"."dbo"."ttdsls045001" ttdsls045001 ON
ttdsls041001."t_orno" = ttdsls045001."t_orno&quot
LEFT OUTER JOIN "mkdaily"."dbo"."ttdsls020001" ttdsls020001 ON
ttdsls041001."t_cuno" = ttdsls020001."t_cuno" AND
ttdsls041001."t_item" = ttdsls020001."t_item&quot
INNER JOIN "mkdaily"."dbo"."ttttxt010001" ttttxt010001 ON
ttdsls020001."t_txta" = ttttxt010001."t_ctxt"}
WHERE
(ttdsls040001."t_cuno" = '900100' OR
ttdsls040001."t_cuno" = '100104' OR
ttdsls040001."t_cuno" = '100103' OR
ttdsls040001."t_cuno" = '100102' OR
ttdsls040001."t_cuno" = '100101' OR
ttdsls040001."t_cuno" = '100100') AND
ttdsls045001."t_ddat" >= ? AND
ttdsls045001.&quot;t_ddat&quot; < ?

 

[IdoMillet]

Cases where the OuterJoin succeeds to return
ttdsls040001 records with no matching records
in the other tables would result in Null values
for all columns from those tables.

Your WHERE condition then gets rid of those records
because it doesn't allow for such Null value.

Add an
OR Isnull()
condition to allow for such cases or use an 'ON'
condition instead of WHERE (if your DBMS supports it).

Cheers,
- Ido ixm7@psu.edu

 

[prevost999]

IdoMillet: Thank you. I definitely can make sense of what
you are saying. Where excatly do I place the OrIsnull() clause in the statement? I tried several places but all unsuccessful. thanks....

 

[IdoMillet]

You simply add it to your WHERE clause.

Cheers,
- Ido ixm7@psu.edu

 

[prevost999]

IdoMillet: Could you possibly illustrate this? Thanks!

 

[prevost999]

I got it to work using advice from another thread. From the 2nd join forward, I changed the inner joins all to left outer joins, and it worked! Ido thanks for your effort!!