CODEWITHSUNDEEP
phpmysqlmysqli
Nio
Nio

Reputation: 527

Mysql create temporary table and fetch filtered data

I have a the following MySQL tables (MySQL 5.6):

contacts

+----+----------------+
| id |     email      |
+----+----------------+
|  1 | [email protected] |
|  2 | [email protected] |
+----+----------------+

contact_attributes

+----+-------------+
| id |    name     |
+----+-------------+
|  1 | name        |
|  2 | current_car |
|  3 | car_color   |
+----+-------------+

contact_attribute_values

+----+------------+-------------------------+------------+
| id |   value    | contact_attribute_value | contact_id |
+----+------------+-------------------------+------------+
|  1 | John Doe   |                       1 |          1 |
|  2 | Eva Milton |                       1 |          2 |
|  3 | BMW X3     |                       2 |          1 |
|  4 | Volvo XC90 |                       2 |          2 |
|  5 | Pink       |                       3 |          1 |
|  6 | Blue       |                       3 |          2 |
+----+------------+-------------------------+------------+

Is there a way to create a temporary table with the following schema in MySQL to fetch and filter the contacts by attributes?

+------------+----------------+------------+-------------+-----------+
| contact_id | contact_email  |    name    | current_car | car_color |
+------------+----------------+------------+-------------+-----------+
|          1 | [email protected] | John Doe   | BMW X3      | Pink      |
|          2 | [email protected] | Eva Milton | Volvo XC90  | blue      |
+------------+----------------+------------+-------------+-----------+

Thanks for your help

Upvotes: 0

Views: 353

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

You could create a view which generates this result:

CREATE VIEW contacts_view AS
SELECT
    c.id,
    c.email,     -- OK to select this since we GROUP BY the primary key
    MAX(CASE WHEN ca.name = 'name'        THEN cav.value END) AS name,
    MAX(CASE WHEN ca.name = 'current_car' THEN cav.value END) AS current_car,
    MAX(CASE WHEN ca.name = 'car_color'   THEN cav.value END) AS car_color
FROM contacts c
LEFT JOIN contact_attribute_values cav
    ON c.id = cav.contact_id
INNER JOIN contact_attributes ca
    ON cav.contact_attribute_value = ca.id
GROUP BY
    c.id;

SQLFiddle

You could create a temporary table which has the output from the above view. But I sense that a view is more of what you are after here. A temporary table would be deleted after your session ends, which means you have to populate again each time. And, the data inside would get stale as soon as the data in your underlying tables changes.

Upvotes: 3

user9152295
user9152295

Reputation:

Use CASE expression like this:

SELECT
  v.contact_id,
  MAX(CASE WHEN a.name = 'contact_email' THEN v.value ELSE '' END) AS 'contact_email',
  MAX(CASE WHEN a.name = 'name' THEN v.value ELSE '' END) AS 'name',
  MAX(CASE WHEN a.name = 'current_car' THEN v.value ELSE '' END) AS 'current_car'
  MAX(CASE WHEN a.name = 'car_color' THEN v.value ELSE '' END) AS 'car_color'
FROM contacts as c
INNER JOIN contact_attribute_values as v on c.id = v.contact_id
INNER JOIN contact_attributes as a on v.contact_attribute_value = a.id
group by v.contact_id;

And you don't have to write down all the attributes, you can make it dynamically like this:

SET @cols = NULL;
SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(a.name = ''',
      a.name, ''', v.value , NULL)) AS ', '''', a.name , '''')
  ) INTO @cols
FROM contact_attributes a;


SET @sql = CONCAT('SELECT 
  v.contact_id,', @cols, '
    FROM contacts as c
LEFT JOIN contact_attribute_values as v on c.id = v.contact_id
INNER JOIN contact_attributes as a on v.contact_attribute_value = a.id
group by v.contact_id;');

prepare stmt 
FROM @sql;

execute stmt;

Upvotes: 1

Related Questions