How to see if one column in a table matches a column in another table, when both tables are filtered with a WHERE clause
-
Hi - I have two tables in the same database. Table1 has a structure like this:
AID IsStaffTask
1 0
2 1
3 1
4 0
Table2 has a structure like this:
DID AID
14 1
14 2
15 1
15 2
15 3
What I need to do is verify, for a particular DID, that Table2 has all AID's where IsStaffTask = 1. So, for example, DID 14 does not meet the requirement: it has AID 2, but not 3. DID 15 does meet the requirement: both AID 2 and 3 exist for DID 15. Note: I'm not just concerned with the COUNT of AID's for a DID. I need to verify that there is an AID = 2 and an AID = 3. Also, I can't hard code the values, since Table1's data could change (e.g., we could add to Table1 an AID 5 where IsStaffTask = 1). How do I query this? Thanks!
-
Hi,
Try:
withCTE_T as
(
select COUNT(*) as TotIsStaffTask
from Table1
where IsStaffTask = 1
),
CTE_D as
(
select
b.DID,
COUNT(*) as TotIsStaffTask
from Table2 as b
join Table1 as a
on a.AID = b.AID and a.IsStaffTask = 1
group by b.DID
)
select d.DID
from CTE_D as d
join CTE_T as t
on t.TotIsStaffTask = d.TotIsStaffTask
Hope this helps.
-
Hmmm.... Thank you, but if I understand this script, it's just comparing the *number* of total AID's where IsStaffTask = 1 in both tables. I don't just want to compare the totals, I need to verify that each AID in Table1 where IsStaffTask = 1 has a corresponding AID in Table2 for a given DID. So I need to verify that in my example, Table2 has an AID = 2 and an AID = 3 where DID = 15. I'm beginning to think I should just pull the 2 tables into my C# layer and do the comparison there.
If I'm misunderstanding your script, feel free to correct me! And thanks! 🙂
-
Table DDL, sample data in the form of INSERT statements, and expected results would have been helpful. In their absence, this is untested:
SELECTAID
FROM
Table1 t1
LEFT JOIN
Table2 t2 IN t1.AID = t2.AID
WHERE
t1.IsStaffTeak = 1
AND
t2.AID IS NULL
John
-
Try this
DECLARE @Table1 TABLE(AID INT, IsStaffTask INT)INSERT INTO @Table1(AID,IsStaffTask)
SELECT 1, 0 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 4, 0;
DECLARE @Table2 TABLE(DID INT, AID INT)
INSERT INTO @Table2(DID,AID)
SELECT 14, 1 UNION ALL
SELECT 14, 2 UNION ALL -- 14 is missing 3
SELECT 15, 1 UNION ALL -- 15 has all of 2 and 3
SELECT 15, 2 UNION ALL
SELECT 15, 3;
SELECT t2.DID
FROM @Table2 t2
INNER JOIN @Table1 t1 ON t1.AID=t2.AID
AND t1.IsStaffTask=1
GROUP BY t2.DID
HAVING COUNT(*) < (SELECT COUNT(*) FROM @Table1 WHERE IsStaffTask=1);
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Ten Centuries - thanks, but this is still just comparing COUNTs. I need to verify for DID = 15 not just that there are 2 rows where AID's IsStaffTask = 1, but that those two AID's are 2 and 3. I need to verify that, for example, there aren't 2 AID's = 3, due to some programming bug.
SSCarpal Tunnel - I'd love to send you DDL's and INSERT statements, but everytime I try, my browser crashes. I'm enclosing my code in
blocks, so I don't know what the deal is. Any suggestions would be most welcome.Thanks, everyone!
-
It may be that your websweeper blocks certain keywords. I've had that happen to me before. Try attaching a .txt file instead.
Please make sure that your provide enough sample data to cover every case (what to do if there are no AIDS, exactly one, more than one, etc).
John
-
Melanie Peterson (11/14/2012)
Ten Centuries - thanks, but this is still just comparing COUNTs. I need to verify for DID = 15 not just that there are 2 rows where AID's IsStaffTask = 1, but that those two AID's are 2 and 3. I need to verify that, for example, there aren't 2 AID's = 3, due to some programming bug.Thanks, everyone!
Couple of DISTINCTs should help
DECLARE @Table1 TABLE(AID INT, IsStaffTask INT)
INSERT INTO @Table1(AID,IsStaffTask)
SELECT 1, 0 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 4, 0;
DECLARE @Table2 TABLE(DID INT, AID INT)
INSERT INTO @Table2(DID,AID)
SELECT 14, 1 UNION ALL
SELECT 14, 2 UNION ALL
SELECT 14, 2 UNION ALL
SELECT 14, 2 UNION ALL
SELECT 15, 1 UNION ALL
SELECT 15, 1 UNION ALL
SELECT 15, 2 UNION ALL
SELECT 15, 2 UNION ALL
SELECT 15, 3;
SELECT t2.DID
FROM @Table2 t2
INNER JOIN @Table1 t1 ON t1.AID=t2.AID
AND t1.IsStaffTask=1
GROUP BY t2.DID
HAVING COUNT(DISTINCT t2.AID) < (SELECT COUNT(DISTINCT AID) FROM @Table1 WHERE IsStaffTask=1);
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
OK, I've attached a file with DDL's for the two tables. I've dropped Table1 and Table2 and given them their real names. Same for column names. Makes it more comprehensible, I think.
With the data attached, LitHoldDetailsID = 14 from the AuditTrail table has two ActionID's, 1 and 2. Only ActionID 2 in the Actions table has IsStaffTask = 1, so it LitHoldDetailsID = 14 does not meet the application requirements (LitHoldDetailsID with ALL IsStaffTask = 1 ActionID's). LitHoldDetailsID = 15, however, does meet the requirements, since it has both ActionID's 2 and 3 and they are all of the Actions in the Actions table where IsStaffTask = 1. What I want to do is input a LitHoldDetailsID and find out if each ActionID from the Actions table where IsStaffTask = 1 has a corresponding row in the AuditTrail table for that LitHoldDetailsID. Hope this makes sense.
Thanks!
-
There may be more than one row in Table2 with the same DID and AID?
If may be necessary to adapt the script I suggested.
If not, I think the script I suggested is correct, because totals are equal only if there are all AIDs in Table2.
-
Yes, that's the issue. There *shouldn't* be more than one row in Table2 with the same DID and AID, but there's no constraint against it and it could happen due to some programming bug. I want to be sure that the AID's really match the ones in Table1, one AID for each DID in Table2. Hence, just counting doesn't work for me.
-
Mark (sorry I called you Ten Centuries - I'm a little frazzled today) - think you're right. I think the DISTINCTs do give me what I need. I'm interested in seeing what others come up with and I need to ponder this further (see frazzled, above). But I think you've given me my solution. Thank you!
-
Mark - I replied to your second script, but don't see it now. So if you got it, fine, if not, I think you're script is what I'm looking for. I'd be interested in seeing any other approaches that others come up with, but THANK YOU!!! 🙂
-
Oh - there's a 2nd page. Sigh. Frazzled. Anyway, for anyone who hasn't gotten the message - I think Mark's solution is valid. I'm going to go bang my head against a wall now. :w00t:
-
Here is my suggestion with the adaptation (Distinct):
withCTE_T as
(
select COUNT(*) as TotIsStaffTask
from Table1
where IsStaffTask = 1
),
CTE_D as
(
select
b.DID,
COUNT(distinct b.AID) as TotIsStaffTask
from Table2 as b
join Table1 as a
on a.AID = b.AID and a.IsStaffTask = 1
group by b.DID
)
select d.DID
from CTE_D as d
join CTE_T as t on t.TotIsStaffTask = d.TotIsStaffTask
Hope this helps.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply