I have a complex SQL query and having an issue with NULL showing when joining a second table with multiple columns and trying to filter on one column. When I add a where to the column then the other column shows NULL.
Here is a simple example showing the data
Computer Parts table
| ComputerID | PartID |
|---|---|
| 1 | 1 |
| 1 | 3 |
| 1 | 7 |
| 2 | 2 |
| 2 | 4 |
| 3 | 4 |
| 4 | 5 |
| 3 | 2 |
I have a complex SQL query and having an issue with NULL showing when joining a second table with multiple columns and trying to filter on one column. When I add a where to the column then the other column shows NULL.
Here is a simple example showing the data
Computer Parts table
| ComputerID | PartID |
|---|---|
| 1 | 1 |
| 1 | 3 |
| 1 | 7 |
| 2 | 2 |
| 2 | 4 |
| 3 | 4 |
| 4 | 5 |
| 3 | 2 |
Parts table
| PartID | PartName | PartType |
|---|---|---|
| 1 | 2TB SSD | 1 |
| 2 | 1TB SSD | 1 |
| 3 | 512GB Memory | 2 |
| 4 | 128GB Memory | 2 |
| 5 | 64GB Memory | 2 |
| 6 | Windows 10 | 3 |
| 7 | Windows 11 | 3 |
When I use the following query to show computers with a 128GB Memory, the Drive is NULL.
SELECT
MAX(ComputerID) AS [Computer ID],
MAX(PDrive.PartName) AS Drive,
MAX(PMemory.PartName) AS Memory
FROM
ComputerParts
LEFT OUTER JOIN
dbo.Parts PDrive ON ComputerParts.PartID = PDrive.PartID
AND PDrive.PartType = 1
LEFT OUTER JOIN
dbo.Parts PMemory ON ComputerParts.PartID = PMemory.PartID
AND PMemory.PartType = 2
WHERE
PMemory.PartName = '128GB Memory'
GROUP BY
ComputerParts.ComputerID;
Results:
Results of query
| Computer ID | Drive | Memory |
|---|---|---|
| 2 | NULL | 128GB Memory |
| 3 | NULL | 128GB Memory |
How do I change my query to get the following result?
| Computer ID | Drive | Memory |
|---|---|---|
| 2 | 1TB SSD | 128GB Memory |
| 3 | 1TB SSD | 128GB Memory |
Thank you in advance.
SELECT
cp.computer_id,
MAX(CASE WHEN p.part_type = 1 THEN p.part_name END) AS drive,
MAX(CASE WHEN p.part_type = 2 THEN p.part_name END) AS memory
FROM computer_parts cp
JOIN parts p ON cp.part_id = p.part_id
GROUP BY cp.computer_id
HAVING MAX(CASE WHEN p.part_type = 1 THEN p.part_name END) IS NOT NULL
AND MAX(CASE WHEN p.part_type = 2 THEN p.part_name END) = '128GB Memory';
maybe you can try this. you only need join once and use max to pivot each row, max is used to ensure only return one row for each component (drive/memory) and select non-null component
As @riz already mentioned - what you are trying to do is horizontal pivoting - from vertical to horizontal, and the technique is what @riz showed you.
I personally prefer pivoting all I can pivot, and if NULLs occur, then fine by me. SQL Server, which you seem to be using, actually issues the warning:
Warning: Null value is eliminated by an aggregate or other SET operation
So my attempt looks like this:
WITH
-- your input ....
computerparts(computerid,partid) AS (
SELECT 1,1
UNION ALL SELECT 1,3
UNION ALL SELECT 1,7
UNION ALL SELECT 2,2
UNION ALL SELECT 2,4
UNION ALL SELECT 3,4
UNION ALL SELECT 3,2
UNION ALL SELECT 4,5
)
,
parts(partid,partname,parttype) AS (
SELECT 1,'2TB SSD',1
UNION ALL SELECT 2,'1TB SSD',1
UNION ALL SELECT 3,'512GB Memory',2
UNION ALL SELECT 4,'128GB Memory',2
UNION ALL SELECT 5,'64GB Memory',2
UNION ALL SELECT 6,'Windows 10',3
UNION ALL SELECT 7,'Windows 11',3
)
-- end of your input, query starts here ..
SELECT
computerid
, MAX (CASE parttype WHEN 1 THEN partname END) AS drive
, MAX (CASE parttype WHEN 2 THEN partname END) AS memory
, MAX (CASE parttype WHEN 3 THEN partname END) AS opsys
FROM parts AS p
JOIN computerparts AS cp ON p.partid = cp.partid
GROUP BY computerid
ORDER BY computerid
;
| computerid | drive | memory | opsys |
|---|---|---|---|
| 1 | 2TB SSD | 512GB Memory | Windows 11 |
| 2 | 1TB SSD | 128GB Memory | null |
| 3 | 1TB SSD | 128GB Memory | null |
| 4 | null | 64GB Memory | null |
Warning: Null value is eliminated by an aggregate or other SET operation.
fiddle