SQL Joining tables and display second table as two columns with filtering on column data - Stack Overflow

admin管理员组

文章数量:1417674

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.

• sql

Share Improve this question Follow edited Feb 3 at 4:55 marc_s 757k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Feb 3 at 0:57 SheldonSheldon 322 bronze badges 2

• Please - learn to use table markdown to properly format your tables ! – marc_s Commented Feb 3 at 4:55
• Please tag your RDBMS – Dale K Commented Feb 3 at 8:19

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

SELECT 
    cpputer_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 cpputer_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

本文标签： SQL Joining tables and display second table as two columns with filtering on column dataStack Overflow