SQL Implied Null Values in a Concatenated Table
When joining two tables with a one-to-one relationship, I want the result to display all values in many tables with values null
in one table.
Here's an example:
tbl_Platform: PriKey = PlatformID
PlatformID PlatformDesc
1 Application
2 Cloud
3 Storage
4 Backup
tbl_Missed: PriKey = CustomerID + Week + PlatformID
CustomerID Week Missed Platform ID
49 1 2017-05-19 1
Desired output:
CustomerID Week Missed PlatformDesc
49 1 2017-05-19 Application
49 1 null Cloud
49 1 null Storage
49 1 null Backup
The closest I've come to is using cross-connect like this:
SELECT
dbo.tbl_Missed.CustomerID,
dbo.tbl_Missed.Week,
dbo.tbl_Missed.Missed,
dbo.tbl_Platform.PlatformDesc
FROM
dbo.tbl_Platform
CROSS JOIN dbo.tbl_MissedSPT
Which gives me:
49 1 2017-05-19 Application
49 1 2017-05-19 Cloud
49 1 2017-05-19 Storage
49 1 2017-05-19 Backup
+3
source to share
1 answer
It seems that you want to take the value of an attribute missed
if there is a match in platformID
and null
otherwise. Try the following (hopefully there are no typos):
SELECT
dbo.tbl_Missed.CustomerID,
dbo.tbl_Missed.week,
CASE WHEN dbo.tbl_Missed.PlatformID = dbo.tbl_Platform.PlatformID
THEN dbo.tbl_Missed.missed
ELSE NULL
END as missed,
dbo.tbl_Platform.PlatformDesc
FROM dbo.tbl_Platform CROSS JOIN dbo.tbl_MissedSPT
+3
source to share