I have a SQL query as below
with Vehicles as (SELECT distinct t1.Col1, t1.Col2, t1.Col3, t1.Col4, t1.customerID, (select top 1 t4.Col1 from table4 As NewColumn where t5.purchaseDate <= CAST(CURRENT_TIMESTAMP as date) order by t5.date desc) As FinalDate FROM table1 t1 INNER JOIN table2 As t2 ON t1.customerID = t2.customerID AND t1.vehicleId = t2.vehicleId INNER JOIN table3 As t3 ON t2.customerID = t3.customerID AND t2.nationId = t3.nationId INNER JOIN table4 As t4 ON t4.vehicleId = t2.vehicleId LEFT JOIN table5 As t5 On t3.customerID = t5.customerID AND t2.vehicleId = t5.vehicleId AND t5.customerID = t4.customerID AND t5.vehicleId = t4.vehicleId AND t5.areamgrId = t4.areamgrId)
Select * from Vehicles where.....
This runs as intended until i add in t4.Col1 (the inner select statement). Adding this in means i get extra rows back as the values in the column are different.
I tried to add some logic to say 'get me the first value where its earlier than todays date' i add a desc so it sorts by the most recent date and then selects that. Even doing this returns extra rows. I would like to do this again for another column with a different where clause too.
What am i doing wrong?