SQL grouping query question; evaluating a group of rows based on the value of one field.

Question

I've got table vendorparts that lists all my parts and their vendor(s). Parts with multiple vendors have multiple records in this table. I'm trying to write a query that only returns the partid, and vendor of parts that do not have a default vendor assigned.

Partid      Vendor     Defaultflag
1           A          1
2           B          0
2           C          0
3           D          0
3           E          0
3           F          1
4           G          0

I would like to return the following:

Partid      Vendor
2           B
2           C
4           G

I'm obviously having issues with partid 3 and getting the query to see it as having a default vendor assigned.

Answer 1

Use an inner select to find the parts who don't have a default vendor. This is the parts that have MAX(Defaultflag) = 0 when grouped by partid. You can then join this back to the original table to get all the rows. Here's the full query:

SELECT T2.Partid, T2.Vendor
FROM (
    SELECT Partid
    FROM Table1
    GROUP BY Partid
    HAVING MAX(Defaultflag) = 0
) T1
JOIN Table1 T2
ON T1.PartId = T2.PartId

Result:

2, 'B'
2, 'C'
4, 'G'

Answer 2

And the null-left-join method:

SELECT vp0.Partid, vp0.Vendor
FROM VendorParts AS vp0
LEFT JOIN VendorParts AS vp1 ON vp1.Partid=vp0.Partid AND vp1.Defaultflag=1
WHERE vp1.Partid IS NULL

Answer 3

You can try something like

DECLARE @VendorParts TABLE(
        Partid INT,
        Vendor VARCHAR(10),
        Defaultflag INT
)
INSERT INTO @VendorParts SELECT  1,'A',1 
INSERT INTO @VendorParts SELECT  2,'B',0 
INSERT INTO @VendorParts SELECT  2,'C',0 
INSERT INTO @VendorParts SELECT  3,'D',0 
INSERT INTO @VendorParts SELECT  3,'E',0 
INSERT INTO @VendorParts SELECT  3,'F',1 
INSERT INTO @VendorParts SELECT  4,'G',0

SELECT *
FROM    @VendorParts vp
WHERE    NOT EXISTS (
                        SELECT  1 
                        FROM    @VendorParts 
                        WHERE   Partid = vp.Partid 
                        AND     Defaultflag = 1
                    )

Output

Partid      Vendor     Defaultflag
----------- ---------- -----------
2           B          0
2           C          0
4           G          0

Answer 4

something like

select 
 partid, 
 vendor 
from 
 vendorparts 
where 
 partid not in (
  select 
    partid 
   from 
    vendorparts 
   where 
    defaultflag = 1
 );