I want to return all rows from a Contracts table but a second WHERE clause yields only rows which are not null. (In other words in the code below the 'CAD' restriction means approx half the possible rows have no value traded in Canadian Dollars and thus are not returned --whereas I want all possible rows returned showing NULL values where applicable).
I figure it's a Left Self Join but am struggling with the syntax (and/or whether I need to do an Inner Select),
MIN (ContractPrice) AS LowPrice,
MAX (ContractPrice) AS HighPrice
WHERE dbo.Contracts.MeasurableID = 2018
AND Contracts.CurrencyCode IN ( 'CAD' )
Use conditional aggregation:
SELECT MeasurableID, EntityID, MIN (CASE WHEN CurrencyCode = 'CAD' THEN ContractPrice END) AS LowPrice, MAX (CASE WHEN CurrencyCode = 'CAD' THEN ContractPrice END) AS HighPrice FROM dbo.Contracts WHERE MeasurableID = 2018 GROUP BY MeasurableID, EntityID ORDER BY MeasurableID, EntityID;