CODEWITHSUNDEEP
mysql
tmarois
tmarois

Reputation: 2470

SQL JOIN as single row with child values as columns and ability to ORDER BY child relationship value

I don't know what the right definition of this is, but its more dynamic than a regular relationship join.

contacts:

id, first_name, last_name

fields:

id, handle, type

field_values:

id, field_id, contact_id, value

The table fields is not that important in this, but wanted to give context.

Example of contacts table: 

id   first_name   last_name
--   -----        --------
1    John         Doe
2    Jane         Smith

Example of field value table: 

id   contact_id   field_id   value
--   -----        --------   ------
1    1            1          Boston
2    1            2          [email protected]
3    2            1          Seattle
3    2            2          [email protected]

In this basic example, you can see that there are 2 fields, one for location (boston, seattle) and one for email. When I put them into a JOIN query they look like this

SELECT * FROM contacts LEFT JOIN field_values ON contacts.id = field_values.contact_id;

Example of contacts JOIN field values table: 

id   first_name   last_name  field_id  value
--   -----        --------   ------    -------
1    John         Doe        1         Boston
1    John         Doe        2         [email protected]
2    Jane         Smith      1         Seattle
2    Jane         Smith      2         [email protected]

TWO QUESTIONS:

1) How do I ORDER BY the field value. So I want to order by the field email which is field id = 2.

2) Is it possible to get a single row for each contact and each field value as a new column?

Example: Single row per contact? 

id   first_name   last_name  field_id(2)          field_id(1)
--   -----        --------   ------               -------
1    John         Doe        [email protected]     Boston
2    Jane         Smith      [email protected]   Seattle

Upvotes: 1

Views: 56

Answers (2)

Dark Knight
Dark Knight

Reputation: 6541

Single row per contact:

SELECT 
    contacts.id,
    contacts.first_name,
    contacts.last_name,
    GROUP_CONCAT(IF(field_values.field_id = 2, field_values.value, NULL)) AS email,
    GROUP_CONCAT(IF(field_values.field_id = 1, field_values.value, NULL)) AS field_1
FROM contacts 
LEFT JOIN field_values ON contacts.id = field_values.contact_id
GROUP BY contacts.id
ORDER BY email;.  -- it is optional, only include if you want to sort result by ascending emails.

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

You may use pivoting logic here to turn out the email and city as separate columns:

SELECT
    c.id,
    c.first_name,
    c.last_name,
    MAX(CASE WHEN fv.field_id = 2 THEN fv.value END) AS email,
    MAX(CASE WHEN fv.field_id = 1 THEN fv.value END) AS city
FROM contacts c
LEFT JOIN field_values fv
    ON c.id = fv.contact_id
GROUP BY
    c.id,
    c.first_name,
    c.last_name;

Upvotes: 0

Related Questions