Oracle SELECT WHERE值存在或不存在 [英] Oracle SELECT WHERE value exists or doesn't exist

问题描述

我有3张桌子； CASES，USERS和USER_META.对于此问题，您需要知道USER_META表具有3列；参见表3. user_id，meta_key和meta_value

I have 3 tables; CASES, USERS and USER_META. For this issue you need to know that the USER_META table has 3 columns; user_id, meta_key and meta_value

每个用户与许多CASES相关联，而每个USER与许多USER_META

Each user is associated with many CASES and each USER is associated with many USER_META

我当前的查询是这样

SELECT CASES.*, USERS.*, USER_META.*
FROM CASES
JOIN USERS ON USERS."user_id" = CASES."user_id"
JOIN USER_META ON USER_META_"user_id" = USERS."user_id"

此方法的问题是每个USER都有USER_META的 ALOT ，因此我的结果集有太多行.如何重写此查询，以便仅选择USER_META."meta_key"等于某个值的USER_META，如果尚未设置此USER_META."meta_key"，仍会得到结果

The problem with this approach is that each USER has A LOT of USER_META so my result set has too many rows. How can I rewrite this query so that I can select only the USER_META where the USER_META."meta_key" is equal to a certain value yet still get the result if they do not have this USER_META."meta_key" set yet

例如:

SELECT CASES.*, USERS.*, USER_META.*
FROM CASES
JOIN USERS ON USERS."user_id" = CASES."user_id"
JOIN USER_META ON USER_META_"user_id" = USERS."user_id"
WHERE USER_META."meta_key" = 'my_key'

这将很好用，但是并非所有用户在"meta_key"列中的值都为"my_key"，我们仍然需要查看他们的CASE.对于没有"meta_key"的用户，结果应仅返回CASE和USER列.

This would work great but not all users have a value of "my_key" in the "meta_key" column and we still need to view their CASE. For users that do not have the "meta_key" the result should just return the CASE and USER columns.

如何重写此查询，以便使用此meta_key和不使用meta_key的用户都能得到结果?

How can I rewrite this query so it gets the result for both users with this meta_key and without?

谢谢，我希望这是有道理的.

Thanks, I hope this makes sense.

推荐答案

我会使用LEFT JOIN

I would use a LEFT JOIN

SELECT CASES.*, USERS.*, USER_META.*
FROM CASES
JOIN USERS ON USERS."user_id" = CASES."user_id"
LEFT JOIN USER_META ON USER_META."user_id" = USERS."user_id" AND USER_META."meta_key" = ?

这篇关于Oracle SELECT WHERE值存在或不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！