I have two tables:

users containing all my users with common attributes
metas holding all dynamic attributes in a one to many relation, (Fyi I cant merge theses attributes into the users table because they can be created / deleted on the fly).

users

id	name	email
1	John	[email protected]
2	Jane	[email protected]

metas

user_id	name	value
1	created_at	"2021-01-01"
1	logged_at	"2021-01-01"
2	created_at	"2021-01-01"

Problem

I want to build a SQL query resulting with something like below, so I can have some where and orders on user metadatas.

id	name	email	created_at	logged_at
1	John	[email protected]	2021-01-01	2021-01-01
2	Jane	[email protected]	2021-01-01	NULL

Progression

I managed to build a result but with only one meta (the created_at) but not logged_at, because I can't group many meta rows for the same user.

SELECT users.*, CAST(JSON_UNQUOTE(value) AS DATE) as created_at
FROM users
LEFT JOIN metas on users.id = metas.user_id and metas.name = 'created_at'
ORDER BY created_at desc

Constraints

It should be done in a single query and can't be done from the code side.

Any suggestions ?

CodePudding user response：

You can join the same table twice.

SELECT users.*, 
  CAST(JSON_UNQUOTE(m1.value) AS DATE) as created_at,
  CAST(JSON_UNQUOTE(m2.value) AS DATE) as logged_at
FROM users
LEFT JOIN metas m1 on users.id = m1.user_id and m1.name = 'created_at'
LEFT JOIN metas m2 on users.id = m2.user_id and m2.name = 'logged_at'
ORDER BY created_at desc

CodePudding user response：

Using a pivot

select *
from
(
    select u.id,u.name as user_name,u.email,m.name as meta_name,m.value
    from @tUsers as u
    left join @tMetas as m on u.id=m.user_id
) as q
pivot 
( 
    max(q.value) for q.meta_name in ([Created_At],[Logged_At])
) as pvt
order by pvt.id