I am attempting to create a mysql query to join 3 tables of data into a single row. However I am having some issues due to the way the user meta table is laid out. The tables structure is as follows:
users
id user_login user_registered
1 user1 18/06/2018
2 user2 18/06/2018
usermeta
user_id meta_key meta_value
1 color blue
1 type train
2 color red
2 type car
member_subscription
user_id status
1 active
2 active
I want to select the user_login of each user by today's date only on user_registered and join their id's with the user_id of both the usermeta table and member_subscription table but only where the status in the member_subscription table is = 'active', and also include relevant meta data to each user as well so I end up with the following:
user_login color type
user1 blue train
user2 red car
Any help is greatly appreciated as i'm really struggling with this one.
For Entity–attribute–value model aka EAV structure to list attributes in different columns you can use conditional aggregation based on your attributes by joining your meta table like
SELECT u.id,
u.user_login,
MAX(CASE
WHEN m.meta_key = 'color' THEN m.meta_value
ELSE NULL
END) AS color,
MAX(CASE
WHEN m.meta_key = 'type' THEN m.meta_value
ELSE NULL
END) AS type
FROM users u
JOIN usermeta m
ON m.user_id = u.id
JOIN member_subscription ms
ON ms.user_id = u.id
WHERE ms.status = 'active'
GROUP BY u.id,
u.user_login