1

I'm sure this has been ask/answered already but I don't know how this kind of action is called and my SQL knowledge is limited.

I'm searching for a single SQL statement to merge two tables:

Table USER:

ID   hash
=========
1    abc
2    def
3    ghi

and the USER_FIELD table:

ID  user_id key        value
=============================
1   1       firstname  John
2   1       lastname   Doe
3   2       firstname  Justin
4   2       lastname   Case

Now I would like to know how I get this view:

ID  hash  firstname   lastname
================================
1   abc   John        Doe

So if I add an addtional entry in the USER_FIELD table with a key "email" I get a new column in the last result

Is this even possible just in MySQL or do I have to go and alter the result in PHP?

Is this even a good DB design or should I drop this an go with a different (which one?)

Yogesh Suthar
  • 30,424
  • 18
  • 72
  • 100
Xaver
  • 11,144
  • 13
  • 56
  • 91

5 Answers5

2

You can do this with a join and a group by:

select u.*, firstname, lastname
from user u join
     (select uf.user_id,
             max(case when key = 'firstname' then value end) as firstname,
             max(case when key = 'lastname' then value end) as lastname
      from user_field uf
      group by user_id
     ) uf
     on uf.user_id = u.id;

You can also do this with a sequence of joins:

select u.*. firstname.value, lastname.value
from user u join
     user_field firstname
     on u.id = firstname.user_id and firstname.key = 'firstname' join
     user_field lastname
     on u.id = lastname.user_id and lastname.key = 'lastname';

Your result seems to limit this to just one user id. You might want a where clause with this filter.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

Generally this isn't considered as a good practice for flat type tables (like user data). This structure is usually used for storing user specified custom fields or meta data where you can have none or hundreds of them for a single instance.

The reason for avoiding this is if you want to get N user property you have to do N-1 join in MySQL and probably dynamically generate the query in the application which is suboptimal. Just put all your standard data in one table and you can use this user_field table for custom fields which only retrieved upon request.

If you're stuck with this structure to be able to do the querying efficiently I would change the user_field table to not have auto_increment id and rather make the user_id and key the PRIMARY KEY for the table. This way all the fields for a user will end up next to each other in the table space (assuming you're using InnoDB) and the lookup will be a primary key lookup. One further step would be to change the key to be tinyint and use lookup IDs instead of actual characters (you can even use CONSTANTS in the code to make it cleaner).

Károly Nagy
  • 1,734
  • 10
  • 13
1

Couple of Joins would do the trick.

SELECT u.id, u.hash, uf_f.value AS firstname, uf_l.value AS lastname
FROM user AS u 
LEFT JOIN user_field AS uf_f ON uf_f.user_id = u.id AND uf_f.key = 'firstname'
LEFT JOIN user_field AS uf_l ON uf_l.user_id = u.id AND uf_l.key = 'lastname'
DevZer0
  • 13,433
  • 7
  • 27
  • 51
0

You want to use a JOIN-statement and use the ID and user_id as the subjects.

Kevin Sjöberg
  • 2,261
  • 14
  • 20
0

Another simple way to fetch data using subquery.

SELECT hash, (select value from USER_FIELD where key = 'firstname' and user_id=6 ) as firstname, (select value from USER_FIELD where key = 'lastname' and user_id=6 ) as lastname FROM `USER` where ID = 6

but it is not good way. You will have to compromise with performance, if database goes large. You should use another design pattern.

Tsimtsum
  • 981
  • 9
  • 27