jdallen75
asked on
Intersecting 2 recordsets based only on one field common to both
Without going into detail about how I arrived at the 2 queries below, what I need to do is identify records that meet these 2 criteria:
1. TestTypeID2 must appear in both recordsets (that is to say, tests were run on both machines 101 and 102).
2. The sum of the total failures between BOTH machines must be 4 or greater.
So, in the sample screenshot attached, you can see in the returned recordsets that:
- Only TestTypeID2 = 32 qualifies
- TestTypeID2 = 33 only had 3 failures total
- TestTypeID2 = 35 only had failures on machine 102
I believe I need some sort of intersection, but only based on the TestTypeID2 field.
Here are the queries:
SELECT PH.MachineID
,PTD2.TestTypeID AS TestTypeID2
,COUNT(PTD2.TestData) AS TotalFails
FROM [Comtech_HFV6].[dbo].[tbl_ PartHistor y] PH
LEFT JOIN tbl_PartTestData PTD
ON PH.PartHistoryKey = PTD.PartHistoryKey
LEFT JOIN tbl_PartTestData PTD2
ON (PTD.PartHistoryKey = PTD2.PartHistoryKey) AND (PTD.TestTypeID + 6 = PTD2.TestTypeID )
WHERE PH.SerialNumber = 'A22222222'
AND PH.MachineID = 101
AND PTD.TestTypeID BETWEEN 26 and 31
AND PH.PassedStatus < 0
AND CAST(PTD2.TestData AS REAL) = 0
AND CAST(PTD.TestData AS REAL) < 900
GROUP BY PTD2.TestTypeID, PH.MachineID
SELECT PH.MachineID
,PTD2.TestTypeID AS TestTypeID2
,COUNT(PTD2.TestData) AS TotalFails
FROM [Comtech_HFV6].[dbo].[tbl_ PartHistor y] PH
LEFT JOIN tbl_PartTestData PTD
ON PH.PartHistoryKey = PTD.PartHistoryKey
LEFT JOIN tbl_PartTestData PTD2
ON (PTD.PartHistoryKey = PTD2.PartHistoryKey) AND (PTD.TestTypeID + 6 = PTD2.TestTypeID )
WHERE PH.SerialNumber = 'A22222222'
AND PH.MachineID = 102
AND PTD.TestTypeID BETWEEN 26 and 31
AND PH.PassedStatus < 0
AND CAST(PTD2.TestData AS REAL) = 0
AND CAST(PTD.TestData AS REAL) < 900
GROUP BY PTD2.TestTypeID, PH.MachineID
Sample-Recordsets.jpg
1. TestTypeID2 must appear in both recordsets (that is to say, tests were run on both machines 101 and 102).
2. The sum of the total failures between BOTH machines must be 4 or greater.
So, in the sample screenshot attached, you can see in the returned recordsets that:
- Only TestTypeID2 = 32 qualifies
- TestTypeID2 = 33 only had 3 failures total
- TestTypeID2 = 35 only had failures on machine 102
I believe I need some sort of intersection, but only based on the TestTypeID2 field.
Here are the queries:
SELECT PH.MachineID
,PTD2.TestTypeID AS TestTypeID2
,COUNT(PTD2.TestData) AS TotalFails
FROM [Comtech_HFV6].[dbo].[tbl_
LEFT JOIN tbl_PartTestData PTD
ON PH.PartHistoryKey = PTD.PartHistoryKey
LEFT JOIN tbl_PartTestData PTD2
ON (PTD.PartHistoryKey = PTD2.PartHistoryKey) AND (PTD.TestTypeID + 6 = PTD2.TestTypeID )
WHERE PH.SerialNumber = 'A22222222'
AND PH.MachineID = 101
AND PTD.TestTypeID BETWEEN 26 and 31
AND PH.PassedStatus < 0
AND CAST(PTD2.TestData AS REAL) = 0
AND CAST(PTD.TestData AS REAL) < 900
GROUP BY PTD2.TestTypeID, PH.MachineID
SELECT PH.MachineID
,PTD2.TestTypeID AS TestTypeID2
,COUNT(PTD2.TestData) AS TotalFails
FROM [Comtech_HFV6].[dbo].[tbl_
LEFT JOIN tbl_PartTestData PTD
ON PH.PartHistoryKey = PTD.PartHistoryKey
LEFT JOIN tbl_PartTestData PTD2
ON (PTD.PartHistoryKey = PTD2.PartHistoryKey) AND (PTD.TestTypeID + 6 = PTD2.TestTypeID )
WHERE PH.SerialNumber = 'A22222222'
AND PH.MachineID = 102
AND PTD.TestTypeID BETWEEN 26 and 31
AND PH.PassedStatus < 0
AND CAST(PTD2.TestData AS REAL) = 0
AND CAST(PTD.TestData AS REAL) < 900
GROUP BY PTD2.TestTypeID, PH.MachineID
Sample-Recordsets.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SELECT a.TestTypeID2 FROM (yourQuery1) a
JOIN (yourQuery2) b
ON a.TestTypeID2 = b.TestTypeID2
WHERE a.TotalFails + b.TotalFails >= 4
But otherwise, nice and concise... thanks!