I have a query in SQL Server where I'm doing multiple left joins. At one point I get to:
...LEFT JOIN foo on a.id = foo.id AND foo.code = 100
Up to that join statement in the query, a.id is not repeated; there's only 1 row for every a.id. But table a and foo have a 1-to-many relationship, so after that join statement I get multiple rows with the same a.id.
Table foo will always have multiple records where foo.id = a.id, but it may or may not have a record where foo.id = a.id AND foo.code = 100. If it does, there will be only 1 such record. So, what I want to see in the final query results is just 1 row for every a.id; if foo has a record where f.id = a.id and f.code = 100, then I want to see the date values from f.date1 and f.date2 from that record. If foo doesn't have such a record, then I just want to see NULL values for f.date1 and f.date2.
Edit from the comments:
It turns out that table foo actually can have multiple rows where a.id = foo.id and foo.code = 100.