Simple sql query help

Other SQL Server Topics
1

Hopefully this is the right section and I've no idea how to title this. I'm new to sql. Getting to grips - slowly.
Need help please

I have a table WIRES with columns ID(key), WTYPE, WSIZE.
Also have a table PARTS with columns ID(key), PNAME, WIRES_ID
Then I do a
SELECT WIRES.WTYPE, WIRES.WSIZE, PARTS.PNAME
FROM WIRES
LEFT JOIN PARTS
ON WIRES.ID = PARTS.WIRES_ID

This correctly gives me a list of wires and part names using them.
I get null for PNAME when a wire has no using part. correct.
But how can i filter out all rows where PNAME is null but only when there is another row with matching WTYPE WSIZE pair which has a PNAME not null

e.g.

WTYPE WSIZE PNAME
CH         1          null     <-- this line to be removed
CH         1          Bolt
TY          3         Nut
etc.

WTYPE WSIZE PNAME
CH         1          null     <-- this line NOT to be removed
TY          3         Nut
etc.
2

Hi,
Not sure if I understood your question, but you can try this and see from there.

SELECT w.WTYPE, w.WSIZE, p.PNAME
FROM WIRES as w
 LEFT JOIN PARTS as p
 ON w.ID = p.WIRES_ID
 WHERE
  (
  p.pname is null
  and not exists(select 1 
              from parts as s
              where p.wires_id =s.wires_id
               and s.pname is not null
               and p.id <> s.id)
   )
 or 
 ( p.pname is not null)
WTYPE WSIZE PNAME
ch 1 bolt
Ty 3 Nut

dbFiddle

1 Like
3

or:

WITH WireParts
AS
(
	SELECT W.WType, W.WSize, P.PName
		,ROW_NUMBER() OVER (PARTITION BY W.WType, W.WSize ORDER BY P.PName DESC) AS rn
	FROM Wires W
		LEFT JOIN Parts P
			ON W.ID = P.Wires_ID
)
SELECT WType, WSize, PName
FROM WireParts
WHERE PName IS NOT NULL
	OR rn = 1;
2 Likes
4

Perfect!
Works.
What on earth is it doing though. I need to read about OVER and PARTITION :slight_smile: