LEFT JOIN WITH AND Problem!

Hi all,

Very Frustrated!!!!

I am trying to execute the follow Access (Jet SQL) query.

As i understand it LEFT JOIN returns the results of the first table even if
there is no
matching results in the second table.

This is ok until i introducts a WHERE or AND clause to filter the results in
the second table.

'query to retrieve company & address details
SELECT DISTINCTc.Company_Name, a.Address1 FROM Company c
LEFT OUTER JOIN Address a ON (c.ID = a.Link_ID)
WHERE c.ID = 1
AND a.Address_Type = 'CMP'


In this query the company details exist, but don't get returned because of
the filter in the where clause "a.Address_Type = 'CMP'" i am using to filter
the second table.

How do i get the company details returned while still providing the
appropriate filters..???

Can some one help ...!!!

Cheers,
Adam

Re: LEFT JOIN WITH AND Problem!

AJ wrote:
> Hi all,
>
> Very Frustrated!!!!
>
> I am trying to execute the follow Access (Jet SQL) query.
>
> As i understand it LEFT JOIN returns the results of the first table
> even if there is no
> matching results in the second table.
>
> This is ok until i introducts a WHERE or AND clause to filter the
> results in the second table.
>
> 'query to retrieve company & address details
> SELECT DISTINCTc.Company_Name, a.Address1 FROM Company c
> LEFT OUTER JOIN Address a ON (c.ID = a.Link_ID)
> WHERE c.ID = 1
> AND a.Address_Type = 'CMP'
>
>
> In this query the company details exist, but don't get returned
> because of the filter in the where clause "a.Address_Type = 'CMP'" i
> am using to filter the second table.
>
> How do i get the company details returned while still providing the
> appropriate filters..???
>


You need to use a subquery:
SELECT DISTINCT
c.Company_Name, a.Address1
FROM Company c LEFT JOIN
(SELECT Link_ID, Address1 FROM Address
WHERE Address_Type = 'CMP') As a
ON (c.ID = a.Link_ID)
WHERE c.ID = 1

If this is a filter that would be useful in other queries besides this
one, I would create a saved query with that sql, perhaps calling it
"CMPAddresses". Then the above could be simplified as follows:
SELECT DISTINCT
c.Company_Name, a.Address1
FROM Company c LEFT JOIN CMPAddresses As a
ON (c.ID = a.Link_ID)
WHERE c.ID = 1


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.