Reputation: 45
My table structure looks like this.
Table user
user_id | user_name
1 | user1
2 | user2
3 | user3
Table user_key_value
id | user_id | key | value
1 | 1 | k1 | v1
2 | 1 | k2 | v2
3 | 2 | k1 | v1
4 | 2 | k2 | v22
5 | 3 | k1 | v11
One user can have 0 or more key value pairs. Different users can have different values for the same key. I want to find those users who either don't have row with key k2 or if they have then the value should only be v22 i.e. user2 and user3 in the above example. I am unable to come up with a condition that satisfies this case.
I have tried left outer join and where not exists but both give me those users also who have a row with k2 and value not v22 (user1 because it has a row with k1 which satisfies the where not exists condition and is included in the result)
select u.user_id
from user u
join user_key_value kv on u.user_id=kv.user_id
where not exists (select 1 from user_key_value kv1 where kv1.user_id=kv.user_id and kv.key='k2')
or kv.value = 'v22';
Upvotes: 0
Views: 1229
Reputation: 6750
What you need is to filter out users, who have k2
values other than v22
. So let's just filter them out with not in
or not exists
depending on tables' size and indexes on table with key-values (I hope you have no null
values for user_id
). But with general tables they both will do hash join anti.
select * from u where user_id not in ( select kv.user_id from kv where k = 'k2' and val != 'v22' )
USER_ID | USER_NAME ------: | :-------- 2 | user2 3 | user3
select * from table(dbms_xplan.display_cursor(format=>'TYPICAL +PREDICATE'));
| PLAN_TABLE_OUTPUT | | :-------------------------------------------------------------------------- | | SQL_ID gxp71hkxpq7kz, child number 0 | | ------------------------------------- | | select * from u where user_id not in ( select kv.user_id from kv | | where k = 'k2' and val != 'v22' ) | | | | Plan hash value: 1497905987 | | | | --------------------------------------------------------------------------- | | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | | --------------------------------------------------------------------------- | | | 0 | SELECT STATEMENT | | | | 6 (100)| | | | |* 1 | HASH JOIN ANTI NA | | 1 | 19 | 6 (0)| 00:00:01 | | | | 2 | TABLE ACCESS FULL| U | 3 | 27 | 3 (0)| 00:00:01 | | | |* 3 | TABLE ACCESS FULL| KV | 2 | 20 | 3 (0)| 00:00:01 | | | --------------------------------------------------------------------------- | | | | Predicate Information (identified by operation id): | | --------------------------------------------------- | | | | 1 - access("USER_ID"="KV"."USER_ID") | | 3 - filter(("VAL"<>'v22' AND "K"='k2')) | | |
select * from u where not exists ( select kv.user_id from kv where k = 'k2' and val != 'v22' and u.user_id = kv.user_id )
USER_ID | USER_NAME ------: | :-------- 2 | user2 3 | user3
select * from table(dbms_xplan.display_cursor(format=>'TYPICAL +PREDICATE'));
| PLAN_TABLE_OUTPUT | | :-------------------------------------------------------------------------- | | SQL_ID 3h1mrqrrtgtvp, child number 0 | | ------------------------------------- | | select * from u where not exists ( select kv.user_id from kv | | where k = 'k2' and val != 'v22' and u.user_id = kv.user_id ) | | | | Plan hash value: 3036374083 | | | | --------------------------------------------------------------------------- | | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | | --------------------------------------------------------------------------- | | | 0 | SELECT STATEMENT | | | | 6 (100)| | | | |* 1 | HASH JOIN ANTI | | 1 | 19 | 6 (0)| 00:00:01 | | | | 2 | TABLE ACCESS FULL| U | 3 | 27 | 3 (0)| 00:00:01 | | | |* 3 | TABLE ACCESS FULL| KV | 2 | 20 | 3 (0)| 00:00:01 | | | --------------------------------------------------------------------------- | | | | Predicate Information (identified by operation id): | | --------------------------------------------------- | | | | 1 - access("U"."USER_ID"="KV"."USER_ID") | | 3 - filter(("VAL"<>'v22' AND "K"='k2')) | | |
db<>fiddle here
Upvotes: 1
Reputation: 1257
You have to first check users who don't have k2 at all .. and in the second step to check those who have that but with a corresponding value.. then you join those together :)
-- user list
WITH w_users AS (
SELECT 1 as user_id, 'user1' as user_name FROM dual
UNION ALL SELECT 2 as user_id, 'user2' as user_name FROM dual
UNION ALL SELECT 3 as user_id, 'user3' as user_name FROM dual
)
-- user key value table
, w_user_kv AS (
SELECT 1 as id, 1 as user_id, 'k1' as key, 'v1' as value FROM dual
UNION ALL SELECT 2, 1, 'k2', 'v2' FROM dual
UNION ALL SELECT 3, 2, 'k1', 'v1' FROM dual
UNION ALL SELECT 4, 2, 'k2', 'v22' FROM dual
UNION ALL SELECT 5, 3, 'k1', 'v11' FROM dual
)
SELECT u.*
, CASE
WHEN no_k2.user_id IS NOT NULL THEN 'missing_k2'
WHEN k2_v22.user_id IS NOT NULL THEN 'k2_v22'
ELSE null
END as u_status
FROM w_users u
LEFT JOIN ( -- those without k2
SELECT user_id
, max(CASE
WHEN key = 'k2' THEN 1
ELSE 0
END) as has_key_k2
FROM w_user_kv
WHERE 1=1
GROUP BY user_id
HAVING max(CASE
WHEN key = 'k2' THEN 1
ELSE 0
END) = 0 -- no k2
) no_k2
ON no_k2.user_id = u.user_id
LEFT JOIN ( -- those with k2 and v22
SELECT user_id
FROM w_user_kv
WHERE 1=1
AND key = 'k2'
AND value = 'v22'
) k2_v22
ON k2_v22.user_id = u.user_id
WHERE 1=1
AND ( -- to restrict only to users fulfilling the condition
no_k2.user_id IS NOT NULL
OR
k2_v22.user_id IS NOT NULL
)
RESULT:
USER_ID | USER_NAME | U_STATUS
-------------------------------
2 | user2 | k2_v22
3 | user3 | missing_k2
Upvotes: 0
Reputation: 13527
Fro your sample data, It seems you need below query -
SELECT user_id
FROM user_key_value KV
WHERE NOT EXISTS (SELECT 1
FROM user_key_value KV1
WHERE KV1.user_id = KV.user_id
AND KV1.key='k2')
UNION
SELECT user_id
FROM user_key_value KV
WHERE EXISTS (SELECT 1
FROM user_key_value KV1
WHERE KV1.user_id = KV.user_id
AND KV1.key='k2'
AND KV1.value = 'v22')
Upvotes: 0