Mike S Mike S - 3 years ago 148
SQL Question

SQL Return every row even if Null values

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),

SELECT MeasurableID,
EntityID,
MIN (ContractPrice) AS LowPrice,
MAX (ContractPrice) AS HighPrice

FROM dbo.Contracts

WHERE dbo.Contracts.MeasurableID = 2018
AND Contracts.CurrencyCode IN ( 'CAD' )

GROUP BY
dbo.Contracts.MeasurableID,
dbo.Contracts.EntityID

Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download