Database Query - One to Many

Question

I am sure this questions has been answered before, but I have no idea what to search for.

I have 2 tables (coming from Wordpress), Users and User Meta Data. I need to create a query that takes the Meta Data and includes that with the other user data. I am developing independent solution in MS Access that will use WP data, so I cannot use any Wordpress specific functions.

Basically, I need to take these 2 tables:

user_id | username
-------------------
   01   |  bob24
   02   |  james112


meta_id | user_id |  meta_key  | meta_value
-----------------------------------------
01      | 01      | first_name | Bob
02      | 01      | last_name  | Smith
03      | 02      | first_name | James
04      | 02      | last_name  | Jones

And turn it into this:

user_id | username | first_name | last_name
-----------------------------------------
01      | bob24    | Bob        | Smith
02      | james112 | James      | Jones

I am sure there is a word for this, but I don't know what it is called. Can anyone point me in the right direction?

Thanks!

Can a join query do that kind of operation? I'm not sure how I could make that work. Sorry for such a basic question. I'm sure the answer is really simple. — Nate, Sep 03 '14 at 15:21
I think the word you are looking for might be pivot? You are turning row data into columns. — Matt Fellows, Sep 03 '14 at 15:25
Thank you @MattFellows, that is exactly what I mean. I guess a join query is the best way to do that? — Nate, Sep 03 '14 at 15:26

score 1 · Accepted Answer · answered Sep 03 '14 at 15:54

Thanks so much for @GolezTrol for pointing me in the right direction. I did need to pivot the data. I searched and Access has this feature. I created a "cross-tab query." Here is the query in case anyone else has the same problem.

TRANSFORM Last(meta.meta_value) AS LastOfmeta_value
SELECT meta.[user_id], Last(meta.[umeta_id]) AS [Total Of umeta_id]
FROM meta
GROUP BY meta.[user_id]
PIVOT meta.[meta_key];

score 0 · Answer 2 · edited May 23 '17 at 11:48

0

You are probably looking for pivot table, which is a slow and complex process in MySQL. If you like to read more about that, you can read this question.

But since you seem to have exactly two fields, maybe a join will do just fine for you. To do it with a join, join the meta table twice. Once for the first name and once for the last name:

select
  u.user_id,
  u.username,
  fn.meta_value as first_name,
  ln.meta_value as last_name
from
  User u
  left join Meta fn on fn.user_id = u.user_id and fn.meta_key = 'first_name'
  left join Meta ln on ln.user_id = u.user_id and ln.meta_key = 'last_name'

If you want to have only those users that do indeed have a last name, you can change the join to an inner join, which might be slightly faster too.

edited May 23 '17 at 11:48

Community

1
1

answered Sep 03 '14 at 15:27

GolezTrol

114,394
18
182
210

So I tried the pivot table in Access and it showed the data how I need it to appear in a query. I'll try the join you suggested. – Nate Sep 03 '14 at 15:32
Access says that there is a syntax error and I can't pin it down. And ideas? Thank you! – Nate Sep 03 '14 at 15:45
I suppose Access is too polite to suggest what the syntax error might be. – Strawberry Sep 03 '14 at 15:56
Access? Sorry, I based my answer on MySQL since that was the (first) database that was mentioned in the tags. :-) I took the liberty of removing it from the tags now, and `wordpress` as well, since you explicitly state that it needs to be independent from Wordpress. – GolezTrol Sep 03 '14 at 16:11

score -1 · Answer 3 · edited Sep 03 '14 at 15:36

-1

Assume that you have two table name with meta and user

SELECT username
    , meta.meta_value
    , user_id
FROM user
INNER JOIN WHERE user.user_id = meta.user_id;

edited Sep 03 '14 at 15:36

Joël Salamin

3,538
3
22
33

answered Sep 03 '14 at 15:33

sohel4r

133
10

Thanks for the answer, but this didn't work at all. I got an error and changed it to "INNER JOIN meta ON ..." Still nothing. – Nate Sep 03 '14 at 15:44

Database Query - One to Many

3 Answers3