将表B中的多行链接到MySQL中的表A中的一行

Question

I have two tables, for simplicity, table A and table B (Note, I don't have the authority to change the structure of these tables, so I'm asking this question to get around a bad database design).

Table A has two columns:

"id"          - is the unique identifier.
"customer_id" - is the customer's ID.

So table A holds a list of customer IDs.

Table B holds properties about customers. But it does it in a weird way (again, I didn't set this up, and I can't change it). Table B has [NUMBER] columns:

"id"          - is the unique identifier.
"customer_id" - is the customer's ID.
"key"         - is the name of the key/value pair
"value"       - is the value of the key/value pair

So table B holds key/value pairs that are linked to customers by their ID.

I could join these two tables to get something like this:

+----+-------------+------------+-------+
| id | customer_id | key        | value |
+----+-------------+------------+-------+
| 0  | 5           | first_name | Bob   |
| 1  | 5           | last_name  | Jones |
| 2  | 6           | first_name | Sally |
| 3  | 6           | last_name  | Sue   |
+----+-------------+------------+-------+

But as you can see, that can be difficult to manage because information about one customer is on two different rows. What would be ideal is something like this:

+----+-------------+------------+-----------+
| id | customer_id | first_name | last_name |
+----+-------------+------------+-----------+
| 0  | 5           | Bob        | Jones     |
| 1  | 6           | Sally      | Sue       |
+----+-------------+------------+-----------+

With all of the customer's data on one row.

Is there a way to do this in a SQL query so that I don't have to mess with the results in PHP? Or will I have to pick through the data in PHP?

Answer 1

One method is conditional aggregation:

select (@rn := @rn + 1) as id, customer_id,
       max(case when `key` = 'first_name' then value end) as first_name,
       max(case when `key` = 'last_name' then value end) as last_name
from b cross join
     (select @rn := 0) params
group by customer_id;

I'm not sure what table a would be used for, perhaps for filtering the customer ids.