MYSQL issue selecting and sorting from separate tables

Question

I am having difficulty assembling the proper sql statements to list and sort data based upon my needs. Below is the structure of two tables I need to select data from.

For each user in the users table, I need to list id, name, and key[a] and key[b] from users_nfo table.

Table users: 
+----+------+
| id | name |
+----+------+
| 1  | aa   |
| 2  | bb   |
| 3  | cc   |
| 4  | dd   |
| 5  | ee   |
+----+------+

Table users_nfo:
+----+-----+-----+-------+
| id | uid | key | value |
+----+-----+-----+-------+
| 1  | 1   | a   | 22    |
| 2  | 1   | b   | 47    |
| 3  | 2   | a   | 38    |
| 4  | 2   | b   | 16    |
| 5  | 3   | a   | 27    |
| 6  | 3   | b   | 67    |
| 7  | 4   | a   | 75    |
| 8  | 4   | b   | 67    |
| 9  | 5   | a   | 63    |
| 10 | 5   | b   | 67    |
+----+-----+-----+-------+

The result should be similar to this

Array result: 
+----+------+---+---+
| id | name | a | b |
+----+------+---+---+
| 1  | aa   |22 |47 |
| 2  | bb   |38 |16 |
| 3  | cc   |27 |67 |
| 4  | dd   |75 |67 |
| 5  | ee   |63 |67 |
+----+------+---+---+

Additionally, I need to be able to sort by any column key, such as sorted asc by b.

Help is appreciated. Thanks in advance!

Konerak · Accepted Answer · 2012-08-19 18:41:19Z

2

The trick is to join the users_nfo (sic) table twice, and include the key column in the join condition. Like this:

SELECT u.ID, u.name, n1.value, n2.value from USERS u
JOIN users_nfo n1
ON u.id = n1.id AND n1.key = 'a'
JOIN users_nfo n2
ON u.id = n2.id AND n1.key = 'b'
ORDER BY n2.value ASC

answered Aug 19, 2012 at 18:41

Konerak

39.8k13 gold badges102 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Markus Mikkolainen Over a year ago

the problem with storing values like this is that for the generic case you will ofcourse need a configurable amount of joins which is not nice. So you will have to first fetch the user, then the attributes.

Markus Mikkolainen Over a year ago

and if you only have a limited amount of attributes, they can as well be columns.

Barmar Over a year ago

If you're doing the query from a programming language, it can create all the dynamic joins on the fly, after first doing select distinct key from users_nfo.

user1610333 Over a year ago

Actually, I am using PHP and WordPress. I'm selecting data from the wp_users table and wp_usermeta table to display in a list. Not having much luck yet.

Collectives™ on Stack Overflow

MYSQL issue selecting and sorting from separate tables

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related