CODEWITHSUNDEEP
sqljoin
ShadowG
ShadowG

Reputation: 683

SQL JOIN WIth null

What can I do do make my statement return Null when there is no row with D.MetaDataID = '580c215f-54cb-4449-8368-7d740be71973' and the Data Table?

I've tried Left JOIN But nothing changes... :(

SELECT 
D.Value
FROM dbo.Item I
JOIN dbo.Data D ON D.ItemID = I.ID
WHERE  I.ItemTypeID = '14ea6709-1bf8-4d5c-9090-3ace3cc42874' --Instance.album
       AND D.MetaDataID = '580c215f-54cb-4449-8368-7d740be71973' --freepacks

Upvotes: 3

Views: 215

Answers (2)

Dan
Dan

Reputation: 10786

Make it a LEFT JOIN. Left joins will preserve every row from the original table (dbo.Item), even if the table being joined (dbo.Data) would be NULL. Of course, then you also can't require dbo.Data.anything to be nonzero in your on clause so you should probably remove that criterion.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452977

You need a LEFT JOIN and to move the condition on D into the ON clause rather than WHERE. In the WHERE clause this converts your query back into an inner join.

SELECT D.Value
FROM   dbo.Item I
       LEFT JOIN dbo.Data D
         ON D.ItemID = I.ID
            AND D.MetaDataID = '580c215f-54cb-4449-8368-7d740be71973'
--freepacks
WHERE  I.ItemTypeID = '14ea6709-1bf8-4d5c-9090-3ace3cc42874' --Instance.album

Upvotes: 7

Related Questions