How to find the values in table a that are linked to ALL the values in table b?
-
Please help. I want to find the document_id values in table @tblDocRef that are linked to ALL the workorder_id values in table @tblWorkOrder, and are not linked to any other workorder_id values that do not exist in @tblWorkOrder. In other words, I want an exact match on the values in the @tblWorkOrder table. So, for instance, using the data in the script below, the query I want would return a result set containing document_id values 14 and 15. The other two values would be filtered out: 13 has a link to a 4th workorder_id that does not exist in @tblWorkOrder; 17 is linked to only one of the three values in @tblWorkOrder.
DECLARE @tblWorkOrder table
(
workorder_id int
)
-- Each document is a report that contains one or more work orders. The @tblDocRef table lists all the documents that contain any of the work orders listed in the @tblWorkOrder table.
DECLARE @tblDocRef table
(
document_id int,
workorder_id int
)
INSERT INTO @tblWorkOrder
VALUES(816)
INSERT INTO @tblWorkOrder
VALUES(821)
INSERT INTO @tblWorkOrder
VALUES(822)
INSERT INTO @tblDocRef
VALUES(13, 816)
INSERT INTO @tblDocRef
VALUES(13, 821)
INSERT INTO @tblDocRef
VALUES(13, 822)
INSERT INTO @tblDocRef
VALUES(13, 825)
INSERT INTO @tblDocRef
VALUES(14, 816)
INSERT INTO @tblDocRef
VALUES(14, 821)
INSERT INTO @tblDocRef
VALUES(14, 822)
INSERT INTO @tblDocRef
VALUES(15, 816)
INSERT INTO @tblDocRef
VALUES(15, 821)
INSERT INTO @tblDocRef
VALUES(15, 822)
INSERT INTO @tblDocRef
VALUES(17, 816)
-
Try this. I got the desired output...
SELECT x.document_id
FROM (
SELECT document_id, count(*) AS workordercount
FROM @tblDocRef
GROUP BY document_id) x
INNER JOIN
(SELECT a.document_id, count(*) AS workordercount
FROM @tblDocRef a inner join @tblWorkOrder b
ON a.workorder_id = b.workorder_id
GROUP BY a.document_id) y ON x.document_id = y.document_id
CROSS JOIN
(SELECT COUNT(*) AS workordercount
FROM @tblWorkOrder) z
WHERE x.workordercount = y.workordercount AND
x.workordercount = z.workordercount
-
I tried it, and it worked. Nicely done! Thanks so much.
If my tables were likely to have more than 100 or so rows, then I would be more concerned about performance and would probably prefer a solution that did not rely on COUNT (probably using OUTER JOIN, IS NULL, and/or EXISTS instead); but in my case the difference would probably not be significant.
In the version below, I did some minor tweaking. In my stored procedure, I already needed to count the work orders in @tblWorkOrder and assign that count to the local variable @workorders_in_request. By using that local variable, I was able to eliminate the CROSS JOIN and make the query a little more readable. I also renamed the count columns to better reflect their meaning.
DECLARE @workorders_in_request int
SELECT
@workorders_in_request = COUNT(*)
FROM
@tblWorkOrder
SELECT
x.document_id
FROM
(
SELECT document_id, COUNT(*) AS TotalWorkorders
FROM @tblDocRef
GROUP BY document_id
 
xINNER JOIN
(
SELECT a.document_id, COUNT(*) AS MatchingWorkorders
FROM @tblDocRef a
INNER JOIN @tblWorkOrder b
ON a.workorder_id = b.workorder_id
GROUP BY a.document_id
 
yON x.document_id = y.document_id
WHERE
x.TotalWorkorders = y.MatchingWorkorders AND
x.TotalWorkorders = @workorders_in_request
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply