Valid data in A group- SQL Grouping

Transact-SQL
1

Dear Techie,
May some one please help me how to achieve the below scenario.

two tables one called as driver and one called as electronic.

-- Driver table

DECLARE @DRIVER TABLE
(
Parenttype varchar (50),
childtype varchar (50)
)

INSERT @DRIVER

SELECT 'Carbon Composite Resistor','Ceramic %'

-- Electronic Table

DECLARE @ELECTRONIC TABLE
(
PARENTSKU varchar (50),
ROLLOVER varchar (50),
CHILDSKU varchar (50),
TYPE varchar (50)
)

INSERT @ELECTRONIC

SELECT 'BIN19-1405','LEAD','19-1405','Carbon Composite Resistor' UNION ALL
SELECT 'SAM92-140','MERCURY','92-140','Carbon Composite Resistor' UNION ALL
SELECT 'SAB45-155','LEAD','45-155','Carbon Composite Resistor' UNION ALL
SELECT 'NIP69-153','SULPHUR','69-153','Carbon Composite Resistor' UNION ALL
SELECT 'DIP19-1508','LEAD','19-1508','Carbon Composite Resistor' UNION ALL
SELECT 'ZQC140012','ROHS','140012','Carbon Composite Resistor' UNION ALL
SELECT 'LHH543012','ROHS','543012','Carbon Composite Resistor' UNION ALL
SELECT 'JWC592013','ROHS','592013','Carbon Composite Resistor' UNION ALL
SELECT 'GHY846013','ROHS','846013','Carbon Composite Resistor' UNION ALL
SELECT 'ZQC140012','ROHS','140012','Ceramic capacitors LARGE' UNION ALL
SELECT 'LHH543012','ROHS','543012','Ceramic capacitors SMALL' UNION ALL
SELECT 'JWC592013','ROHS','592013','Ceramic capacitors MEDIUM' UNION ALL
SELECT 'GHY846013','ROHS','846013','Ceramic capacitors' UNION ALL
SELECT 'MCN8LTC8K','ROHS','8LTC8K','Double-layer capacitors' UNION ALL
SELECT 'PRM81150','ROHS','81150','Tantalum capacitors' UNION ALL
SELECT 'PRM846013','ROHS','846013','Hybrid capacitors '

Here I am looking for output which meet two below condition

1st: ALL those SKU which contain type which is equal to the Parenttype available in Driver table and rollover other than ROHS

2ND: ALL THOSE SKU Records whose rollover are ROHS but only value present with parent type but not with Child Type which is present in driver table

Expected Output

Thanks a lot.

2

Are you trying to merge 2 different results sets here?

SELECT 
		e.[PARENTSKU],
		e.[ROLLOVER],
		e.[ChildSKU],
		e.[Type]

		FROM @ELECTRONIC e
				WHERE EXISTS(SELECT d.[ParentType]
				FROM @DRIVER d WHERE e.[Type] = d.[ParentType]
				AND e.[ROLLOVER] <> 'ROHS'
		)

		SELECT 
		e.[PARENTSKU],
		e.[ROLLOVER],
		e.[ChildSKU],
		e.[Type]

		FROM @ELECTRONIC e
				WHERE EXISTS(SELECT d.[ParentType]
				FROM @DRIVER d WHERE e.[Type] = d.[ParentType]
				AND e.[ROLLOVER] = 'ROHS'
				AND e.[Type] <> d.[ChildType]
		)
1 Like