Join multiple tables and search parameters

Question

I have database with 2 tables, 1 for storing customers Id and for customer informations. The second table is ordered with key/value because I need to store undefined values without needing to alter customers table.

Table structure

table customers:

=================
    id | status
=================

table customers_info:

 =======================================
   id | id_customer | key | value
 =======================================

Content example:

table customers:

=================
    id | status
    1  | 1
    2  | 1
    3  | 1
==================

table customers_info

=======================================
    id | id_customer | key | value
    1  | 1           | name| Doe
    2  | 1           | age | 25
    3  | 1           | city| NY
    4  | 2           | name| Smith
    5  | 2           | age | 26
    6  | 3           | age | 30
=======================================

I simply query my tables with

SELECT ci.id_customer AS CustomerId, 
       MAX(CASE WHEN ci.key = 'name' THEN ci.value ELSE '' END) AS CustomerName, 
       MAX(CASE WHEN ci.key = 'age' THEN ci.value ELSE '' END) AS CustomerAge, 
       MAX(CASE WHEN ci.key = 'city' THEN ci.value ELSE '' END) AS CustomerCity
FROM customers_info ci 
GROUP BY ci.id_customer
ORDER BY ci.id_customer;

I can get all customers, but cannot query with age for parameter for example, I tried try search every customer with age 25 but with this query I can't get all values for the customer...

SELECT ci.id_customer AS CustomerId, 
       MAX(CASE WHEN ci.key = 'name' THEN ci.value ELSE '' END) AS CustomerName, 
       MAX(CASE WHEN ci.key = 'age' THEN ci.value ELSE '' END) AS CustomerAge, 
       MAX(CASE WHEN ci.key = 'city' THEN ci.value ELSE '' END) AS CustomerCity
FROM customers_info ci 
LEFT JOIN customers_info on customers_info.age = '25'
GROUP BY ci.id_customer
ORDER BY ci.id_customer;

I would like to get

=======================================
   id_customer | name | age | city
   1           | Doe  | 25  | NY
=======================================

Answer 1

I am not sure that the way you are doing this the best way (I am not sure I would use maximums to do this, nor do I think I would create the tables in the way you are doing it - since in this case you are performing several extra operations in order to have the key value table that you are using) - but here is my go -

First, my create statements:

create database CustomerLinkQuestion;

use CustomerLinkQuestion;

create table customers (
id int,
status int
);

create table customer_info (
id int,
id_customer int,
k varchar(255),
v varchar(255)
);

For whatever reason (and this may be because I don't use MySQL all that much) key and value didn't work (probably reserved) so I used k and v for your key and value segments.

The core concept is that you are going to have to use a sub-query of some sort or a view (a view would be better) -

select * From (
SELECT ci.id_customer AS CustomerId,
       MAX(CASE WHEN ci.k = 'name' THEN ci.v ELSE '' END) AS CustomerName,
       MAX(CASE WHEN ci.k = 'age' THEN ci.v ELSE '' END) AS CustomerAge,
       MAX(CASE WHEN ci.k = 'city' THEN ci.v ELSE '' END) AS CustomerCity
FROM customer_info ci
GROUP BY ci.id_customer
ORDER BY ci.id_customer) as sub
where sub.CustomerAge = 25

the inside (the part in parenthesis and called sub) is exactly what you posted (except - for whatever reason on my test server I used "customer" and not "customers"). Then the sub-query, then the where clause cutting down the sub-query.

Again - I would strongly suggest you look at your schema and design since it seems like you are trying to create a code table for something that it simply doesn't make sense. You will end up with a fixed number of fields in the customer table, each tied to a specific customer. When you are doing what you are doing here, you are performing more database operations that are simply not necessary.

Best of luck!

Aleksei