How to retrieve multiple table and format some of its rows in MySQL?

Question

I have the following 3 tables:

custom_data table:

+----+----------------------+
| id | name                 |
+----+----------------------+
| 13 | ACCOUNT NAME         |
| 14 | ACCOUNT NO           |
| 15 | ID NUMBER            |
+----+----------------------+

custom_data_values table:

+----------------+-----------------+-----------+
| custom_data_id | value           | member_id |
+----------------+-----------------+-----------+
|             13 | Ivy Jane Mosendi|        33 |
|             14 | 2199030092      |        33 |
|             15 | 25917781        |        33 |
+----------------+-----------------+-----------+

members table:

+----+----------------------------+
| id | name                       |
+----+----------------------------+
| 30 | John Doe                   |
| 31 | Willy Ong                  |
| 32 | James Reid                 |
| 33 | Ivy Jane Mosendi           |
+----+----------------------------+

I want the following result:

+----+------------------+------------------+-------------+-------------+
| id | name             | ACCOUNT NAME     | ACCOUNT NO  | ID NUMBER   |
+----+------------------+------------------+-------------+-------------+
| 33 | Ivy Jane Mosendi | Ivy Jane Mosendi | 2199030092  | 25917781    |
+----+------------------+------------------+-------------+-------------+

How can I achieve this result with a mysql query?

You need to do it yourselves first / show your current solutions. — reignsly, Oct 11 '16 at 06:44
[MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — Solarflare, Oct 11 '16 at 06:51

score 2 · Accepted Answer · answered Oct 11 '16 at 06:55

2

I finally cracked it. Thanks all

SELECT m.name
        , MAX(CASE WHEN cf.name = 'ACCOUNT NAME' Then cfv.string_value END) As 'ACCOUNT NAME'
        , MAX(CASE WHEN cf.name = 'ACCOUNT NO' Then cfv.string_value END) As 'ACCOUNT NO'
        , MAX(CASE WHEN cf.name = 'ID NUMBER' Then cfv.string_value END) As 'ID NUMBER'
     FROM custom_field_values cfv
     INNER JOIN custom_fields cf
         ON cf.id = cfv.field_id
     INNER JOIN members m
         ON m.id =  cfv.member_id
     WHERE m.id = 33

answered Oct 11 '16 at 06:55

Yves Gonzaga

1,038
1
16
40

Homework-like problem, but with a good solution. Note, also you can accept your own question (to show to the answerers, your problem is already solved). – peterh Oct 11 '16 at 17:45
I clicked checked but it says "You can accept your own answer tomorrow". Thanks for the up by the way – Yves Gonzaga Oct 12 '16 at 04:06

How to retrieve multiple table and format some of its rows in MySQL?

1 Answers1