Extracting rows from a Table which fit conditions referring to another Table in Oracle SQL
I have 2 Tables. I want to extract every TB2_ID in Table 2 for which certain conditions are fulfilled in Table 1. So in the case below, we would extract '2' since it is FULLY fulfilled by both ID '1' and '15' in Table 1. TB2_ID '4' would not be extracted since there is no TB1_ID that FULLY fulfills it (i.e. if TB1_ID '16' also had a condition of 10, then TB2_ID '4' would be extracted). I am using Oracle SQL 10g.
Table 1 looks like:
TB1_ID | Condition
______________________
1 | 10
1 | 11
1 | 12
5 | 10
5 | 11
15 | 10
15 | 11
15 | 12
16 | 11
16 | 14
Table 2 looks like:
TB2_ID | Condition
_______________________
2 | 10
2 | 11
2 | 开发者_高级运维 12
4 | 10
4 | 14
This can most simply specified as: Find any TB2_ID that is not satisfied in table 1:
SELECT DISTINCT TB2_ID
FROM table2 t2
left join table1 t1 on t1.TB1_ID = t2.TB2_ID and t1.Condition = t2.Condition
where t1.TB1_ID is null;
SELECT TABLE2.TB2_ID FROM TABLE1 , TABLE2
WHERE TABLE2.CONDITION = TABLE1.CONDITION
GROUP BY TABLE2.TB2_ID
HAVING COUNT(TABLE2.TB2_ID) > 1;
UPDATE - simplified:
try
SELECT DISTINCT A.TB2_ID FROM
(SELECT TB2_ID, COUNT(*) C FROM TABLE2 T2) A,
(SELECT TB1_ID, COUNT(*) C FROM TABLE1 T1) B
WHERE
A.C <= (SELECT COUNT(*) FROM TABLE1 T1, TABLE2 T2 WHERE T1.CONDITION = T2.CONDITION AND T2.TB2_ID = A.TB2_ID AND T1.TB1_ID = B.TB1_ID) AND
A.C <= B.C
精彩评论