Left join not working

[Beto16]

Hi I have two tables. That I want to do a left join  and its bringing in only data where it's equal  enclose is an example of code 

proc sql;

create table temp2 as

select table1.*, table2.id , table2.date

from table1,table2

where table1.id=table2.id and table2.date Gt table1.date

group by table1.id

having table2.date-table1.date=min(table2.date-table1.date);

create table want as

select temp2.*,table2.id ,table2. Date

from temp2,table2

where temp2.id=table2.id and table2.date gt temp2.date

group by temp2.id

having table2.date-temp2.date=min(table2.date-temp2.date)

quit;

i did did use the left join it didn't work  thanks for assistance 

[Reeza]

None of the code below specifies a left join....

You also have a HAVING clause which filters data so it's not a 'straight left join'. Remove the having clause and see if you get what you expect.

[PGStats · Accepted solution]

The problem is that in a left join, non matching table2 records will show table2.id and table2.date as missing which will cause the having clause to exclude them.  You can try this instead:

proc sql;
create table temp2 as
select 
	table1.*, 
	table2.id as id2, 
	table2.date as date2
from 
	table1 left join
	table2 on table1.id=table2.id and table2.date gt table1.date
group by table1.id
having coalesce(table2.date,table1.date)-table1.date=min(coalesce(table2.date,table1.date)-table1.date);

[Beto16]

Hi Pgstat,

i tried your code with coalesce  I notice you used comma instead of minus I tried both gave me small sample ..currently table 1 has 7k records I only get 84.... What I am doing in the code is if there is match table 1 and table 2 than from table two give the next date of service for example 

table 1 

id.        Date  

a1.        01/01/16 

table 2 

id.        Date 

a1.        01/02/16

a1.        01/06/16

i  want the left join to show 

Id.      Date.              Next service 

a1.    01/01/16            01/02/16

thats what I want to see what I see now is some thing like this

id.         Date.              Next service

a1.         01/1/16.            01/02/16

a1.        01/1/16.              01/06/16

a1.       01/1/16.              01/09/16

is every instance in table 2 is brought over ..hope that make sense thanks