LEFT JOIN Columns With WHERE Clause in Oracle Entity Attribute Value(EAV) Database Tables

Question

I have a USERS table like this

USERS

user_id | username
1         tom
2         sam

I also have a USER_META table like this

USER_META

user_meta_id | user_id | meta_key | meta_value
1              1         active     1
2              1         car        dodge
3              2         active     0
4              2         car        honda

My problem is that I need to select the meta_keys active and and car but only for users who have a value of 1 for active so my result set should be something like this

user_id | user_name | user_meta_id | meta_key | meta_value
1         tom         1              active     1
1         tom         2              car        dodge

I have tried a few things but I cannot get this type of result set. Here is what I thought might work

SELECT * FROM USERS
LEFT JOIN USER_META 
   ON USERS."user_id" = USER_META 
   AND (USER_META."meta_key" = 'active' OR USER_META."meta_key" = 'car') 
WHERE (USER_META."meta_key" = 'active' 
AND USER_META."meta_value" = 1)

the problem with this is that I get a result set that is missing the car meta_key/meta_value

user_id | user_name | user_meta_id | meta_key | meta_value
1         tom         1              active     1

How can I modify the query to get all of the meta information? thanks.

Answer 1

First off, this sort of entity-attribute-value data model is generally a very poor idea. You're basically re-implementing what a relational database gives you out of the box and queries get very unwieldy very quickly. Generally, you'll need to join in the USER_META table N times in order to either get N attributes as columns or in order to have N predicates on your data.

On the data modeling front, future developers will generally be grateful if you avoid creating case-sensitive column names so that they don't have to double-quote every identifier every time.

Since you need two keys, you should be able to do something like this (I assume that meta_value is always stored as a character string even if it represents a numeric or boolean value)

SELECT usr."user_id",
       usr."user_name",
       meta."user_meta_id",
       meta."meta_key",
       meta."meta_value"
  FROM users usr
       JOIN user_meta meta ON (usr."user_id" = meta."user_id")
 WHERE meta."meta_key" in ('active', 'car')
   AND usr."user_id" IN (SELECT active."user_id"
                           FROM user_meta active
                          WHERE active."meta_key" = 'active'
                            AND active."meta_value" = '1' )

which returns the expected results for the data you posted

SQL> ed
Wrote file afiedt.buf

  1  with users as (
  2    select 1 "user_id", 'tom' "user_name" from dual union all
  3    select 2, 'sam' from dual
  4  ),
  5  user_meta as (
  6    select 1 "user_meta_id",
  7           1 "user_id",
  8           'active' "meta_key",
  9           '1' "meta_value" from dual union all
 10    select 2, 1, 'car', 'dodge' from dual union all
 11    select 3, 2, 'active', '0' from dual  union all
 12    select 4, 2, 'car', 'honda' from dual
 13  )
 14  SELECT usr."user_id",
 15         usr."user_name",
 16         meta."user_meta_id",
 17         meta."meta_key",
 18         meta."meta_value"
 19    FROM users usr
 20         JOIN user_meta meta ON (usr."user_id" = meta."user_id")
 21   WHERE meta."meta_key" in ('active', 'car')
 22     AND usr."user_id" IN (SELECT active."user_id"
 23                             FROM user_meta active
 24                            WHERE active."meta_key" = 'active'
 25*                             AND active."meta_value" = '1' )
SQL> /

   user_id use user_meta_id meta_k meta_
---------- --- ------------ ------ -----
         1 tom            1 active 1
         1 tom            2 car    dodge

Answer 2

SELECT * FROM USERS
    LEFT JOIN USER_META ON 
        (USERS."user_id" = USER_META.”user_id” 
         AND (USER_META."meta_key" in ('active', 'car') 
WHERE USERS."user_id" IN (select USER_META."user_id" from USER_META 
                          where USER_META."meta_key" = 'active' 
                          AND USER_META."meta_value" = 1
                         )