Question 246667 in Mysql Question posted in
The official MySQL documentation can be found here.

mySQL condition if LEFT OUTER JOIN table contains a value in a column

I have two database tables: one with user IDs and one containing user metadata (with multiple rows related to the same user ID).

users table structure example

id USERNAME
1 user 1
2 user 2
3 user 3

users_meta data structure example

meta_id user_id meta_key meta_val
1 1 key1 a
2 1 key2 b
3 2 key1 d
4 3 key1 e
5 3 key2 f

I’d like query whether a user has got a metadata database row.

 SELECT * FROM users 
 LEFT OUTER JOIN users_meta ON users.id = users_meta.user_id
 WHERE users_meta.meta_key != "key2"
 GROUP BY id 
 ORDER BY id ASC 
 LIMIT 0, 100

How do I query to get the user id 2 not having a meta value related to key2?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks @Kukuh Gumilang for your reply, it put me somehow on the right track to code a shorter and better solution, perfectly adapting to my complex framework.

    The solution was literally as simple as adding a new join and a condition

    SELECT id, username FROM users
LEFT OUTER JOIN users_meta as ume ON users.id = ume.user_id AND ume.meta_key = 'meta-key-to-check'
LEFT OUTER JOIN users_meta as um0 ON users.id = um0.user_id
WHERE 
    (um0.meta_val = 'meta1' AND um0.meta_value IN ('val1', 'val2')) AND
    AND ume.meta_val IS NULL /* or NOT NULL to check for existing metas */
GROUP BY id 
ORDER BY id ASC 
LIMIT 0, 100

  2. 0

    Try this. I use sub queries, union, and query from query.

    SELECT * FROM
(
    SELECT
        (SELECT users_meta.id FROM users_meta WHERE users_meta.user_id = users.id AND users_meta.meta_key = 'key1') AS meta_id,
        users.id AS user_id,
        users.username,
        'key1' AS meta_key,
        (SELECT users_meta.meta_val FROM users_meta WHERE users_meta.user_id = users.id AND users_meta.meta_key = 'key1') AS meta_val
    FROM
        users
    UNION ALL
    SELECT
        (SELECT users_meta.id FROM users_meta WHERE users_meta.user_id = users.id AND users_meta.meta_key = 'key2') AS meta_id,
        users.id AS user_id,
        users.username,
        'key2' AS meta_key,
        (SELECT users_meta.meta_val FROM users_meta WHERE users_meta.user_id = users.id AND users_meta.meta_key = 'key2') AS meta_val
    FROM
        users
) AS SRC
WHERE
    meta_val IS NULL
ORDER BY
    user_id
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search