I am trying to perform a left join from 2 tables.

Table 1 I need all the rows returned, however in joining table 2 I only want to 'append' the data on the first match if there are numerous matches.

Table 1

Table 2

End Result

For now I have a simple left join that matches as needed, I just need to find a way to not left join the data on the second match.

Any other JOINs I have tried limit the the table_1 data.

I have tried to use coalesce but have not idea if that is even the correct tool.

I imagine if I could track how many matches the left join has per criteria I could use a conditional statement to null the join on anything more then the first match.

My Sql:

SELECT table_1.id, table_1.month, table_2.service_date
FROM table_1
LEFT JOIN table_2 ON table_2.id = table_1.id

I hope my question makes sense and any direction on finding the solution would be appreciated.

CodePudding user response：

Starting from your existing left join query, you could use row_number() to identify the "first" row per id, and then conditional logic:

select t1.id, t1.month, 
    case when row_number() over(partition by t1.id order by t2.month) = 1 then t2.service_date end service_date
from table1 t1
left join table2 t2 on t2.id = t1.id

row_number() ranks records having the same id ; you probably want something more stable than an month as string as an order by column.