Complex Join - Is this possible? 1

[smn198]

I'm quite new to SQL but have seemed to get in a muddle that I cannot get out of. I am trying to do the following:

Suppose I have the following data
Table A
ID Blah Blah blah
1 ...
2 ...
3 ...

Table B
A_ID Type Date
1 Apple 1/5/02
3 Pear 3/6/03
3 Pear 5/3/02
3 Banana

Assume I link the tables where Table A.ID=Table B.A_ID

I want to pull all the data from Table A along with the maximum date of Table B where the Type is Pear.

My problem was where there is nothing in Table B, the WHERE clause which stipulated that Table B.Type = "Pear" filtered them out but adding an or = NULL seemed to work.

This now also filteres out records which have values in Table B which are not Pear. For example, ID 1 will be excluded as it is not equal to NULL or to "Pear"

How do I get around this? I guess I could put another select in the from clause? Performance is a concern as I am using a lot of data.

Many thanks!

 

[PHV]

You want all the occurrences of TableA ?
So, take a look at LEFT outer JOIN:
Select A.ID, A.blah_blah,B.MaxPearDate
FROM TableA A LEFT JOIN (
SELECT A_ID,Max(Date) As MaxPearDate FROM TableB WHERE Type='Pear' GROUP BY A_ID
) B ON A.ID=B.A_ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[smn198]

Thanks for that. I take it, it is not possible to do this without sub queries?

I already have a group in my main query. Would it be faster to group the sub query as well and do the max date in there or to do the max in my main query? Either works fine.