SQLTeam.com | Weblogs | Forums

Sql queries - process of elimination


#1

I have 3 queries that i need to put together into a single query with a single output but just cant seen to get the end result that i need any assistance would be very much appreciated

SampleID | TestID | Microcomments |ProductID |ComapanyID
111101 | Test1 |Satisfactory | ABC123 | 1111
111101 | Test2 |No Test |ABC123 | 1111
111101 | Test3 |Unsatisfactory |ABC123 | 1111
222202 | Test2 |Satisfactory |A21 | 2222
222202 | Test3 |Unsatisfactory |A21 | 2222
333301 | Test1 |Satisfactory |AB11 | 3333
333301 | Test3 |No Test |AB11 | 3333
444403 | Test2 |Unsatisfactory |ABCD123 | 4444
444403 | Test3 |No Test |ABCD123 | 4444
555504 | Test1 |Unsatisfactory |BA123 | 5555
555504 | Test2 |Unsatisfactory |BA123 | 5555
666601 | Test3 |Satisfactory |BBB21 | 6666
666601 | Test2 |Satisfactory |BBB21 | 6666

Query 1

Select distinct sample ID where Microcomment = 'Unsatisfactory'

Query 2
Select Distinct Sample ID where Microcomment = 'Satisfactory' and (only display sampleID's that dont appear in the Query 1)

Query 3
Select Distinct Sample ID where Microcomment = 'No Test' and (only display sampleID's that dont appear in the Query 1 & 2)

final output output

SampleID Microcomments ProductID
111101 Unsatisfactory ABC123
222202 Unsatisfactory A21
333301 Satisfactory AB11
444403 Unsatisfactory ABCD123
555504 Unsatisfactory BA123
666601 Satisfactory BBB21


#2

This should give you what you're looking for...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
	SampleID INT NOT NULL,
	TestID CHAR(5) NOT NULL,
	Microcomments VARCHAR(20) NOT NULL,
	ProductID VARCHAR(10),
	ComapanyID INT NOT NULL 
	);
INSERT #TestData (SampleID, TestID, Microcomments, ProductID, ComapanyID) VALUES
	(111101, 'Test1', 'Satisfactory', 'ABC123', 1111),
	(111101, 'Test2', 'No Test', 'ABC123', 1111),
	(111101, 'Test3', 'Unsatisfactory', 'ABC123', 1111),
	(222202, 'Test2', 'Satisfactory', 'A21', 2222),
	(222202, 'Test3', 'Unsatisfactory', 'A21', 2222),
	(333301, 'Test1', 'Satisfactory', 'AB11', 3333),
	(333301, 'Test3', 'No Test', 'AB11', 3333),
	(444403, 'Test2', 'Unsatisfactory', 'ABCD123', 4444),
	(444403, 'Test3', 'No Test', 'ABCD123', 4444),
	(555504, 'Test1', 'Unsatisfactory', 'BA123', 5555),
	(555504, 'Test2', 'Unsatisfactory', 'BA123', 5555),
	(666601, 'Test3', 'Satisfactory', 'BBB21', 6666),
	(666601, 'Test2', 'Satisfactory', 'BBB21', 6666);

--SELECT * FROM #TestData td

--=====================================================

WITH 
	cte_AddRN AS (
		SELECT 
			td.SampleID, td.Microcomments, td.ProductID,
			RN = ROW_NUMBER() OVER (PARTITION BY td.SampleID ORDER BY CASE td.Microcomments WHEN 'Unsatisfactory' THEN 1 WHEN 'Satisfactory' THEN 2 WHEN 'No Test' THEN 3 ELSE 999 END)
		FROM
			#TestData td
		)
SELECT 
	ar.SampleID, 
	ar.Microcomments,
	 ar.ProductID
FROM
	cte_AddRN ar
WHERE 
	ar.RN = 1;

#3

Hey All,

here's how i manage to get the correct output incase anyone else comes across a similar situation.

SELECT  SampleID, ProductID, CompanyID,
    CASE WHEN COUNT(CASE WHEN MicroComments = 'UnSatisfactory' THEN 1 END) > 0 THEN 'UnSatisfactory'
        WHEN COUNT(CASE WHEN MicroComments = 'Satisfactory' THEN 1 END) > 0 THEN 'Satisfactory'
        WHEN COUNT(CASE WHEN MicroComments = 'No test' THEN 1 END) > 0 THEN 'No test'
    END AS MicroComments

FROM T
GROUP BY SampleID, ProductID, CompanyID;