newbie

I have the following join

FROM TrnDetail LEFT OUTER JOIN
FormData ON TrnDetail.Customer = FormData.KeyField

This returns all records from TrnDetail whether there be an entry in the
FormData table for the given customer. - This is what I want . . .however

If I add a where clause like
Where FormData.Type = '8020'

I only get records where TrnDetail.Customer and FormData.KeyField fields are
equal.

How can I get it to return all records from the TrnDetail table regardless
of whether there is a corresponding entry in the FormData table?

eg.
SELECT TrnDetail.Cust, TrnDetail.Add1, FormData.alpha
FROM TrnDetail LEFT OUTER JOIN
FormData ON TrnDetail.Customer = FormData.KeyField
WHERE FormData.Type = '8020'

Result I want is
0015 USA G
0016 UK null
0017 EURO Y
0016 USA R

At the moment I am getting
0015 USA G
0017 EURO Y
0016 USA R

vishal parkar

Newbie,

Where clause will filter out your entire result set. Extend on clause using "and" operator
Ex:
FROM TrnDetail LEFT OUTER JOIN
FormData ON TrnDetail.Customer = FormData.KeyField
and FormData.Type = '8020'

If this doesn't satisfy your requirement post DDL/ some sample records and expected
resultset.
--
-Vishal

newbie

Great - exactly what I wanted

Thanks for all your help
Al