MySQL concatenates multiple lines
I have a database with 2 tables, 1 for storing customer IDs and for customer information. The second table is ordered with key / value because I need to store undefined values.
Table structure
table clients:
=================
id | status
=================
table customers_info
=======================================
id | id_customer | key | value
=======================================
Content example:
table clients:
=================
id | status
1 | 1
2 | 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
=======================================
How can I query tables to mail all clients with their names
Example:
=================
1 | Doe
2 | Smith
=================
If I just do an inner join, I only get the first key of the table:
SELECT * FROM customers inner join customers_info on customers.id = customers_info.id_customer
+3
source to share
8 answers
Try the following:
SELECT ci.id_customer AS CustomerId, ci.value AS CustomerName
FROM customers_info ci
WHERE ci.key = 'name'
ORDER BY ci.id_customer;
Check SQL FIDDLE DEMO
OUTPUT
| CUSTOMERID | CUSTOMERNAME |
|------------|--------------|
| 1 | Doe |
| 2 | Smith |
:: EDIT ::
To get all fields for clients
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;
Check SQL FIDDLE DEMO
OUTPUT
| CUSTOMERID | CUSTOMERNAME | CUSTOMERAGE | CUSTOMERCITY |
|------------|--------------|-------------|--------------|
| 1 | Doe | 25 | NY |
| 2 | Smith | | |
:: EDIT ::
Get GENDER from Gender Table:
SELECT a.CustomerId, a.CustomerName, a.CustomerAge, a.CustomerCity, g.name AS CustomerGender
FROM (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
) AS a
INNER JOIN customers c ON a.CustomerId = c.id
LEFT OUTER JOIN gender g ON C.genderId = c.id
ORDER BY a.CustomerId;
0
source to share
use Inner join or join for intersection result .. // inner join and join are both identical
try it
SELECT c.id as 'Id', i.value as 'Name'
FROM customers c INNER JOIN customer_info AS i
ON c.id = i.id_customer
// OR
SELECT c.id as 'Id', i.value as 'Name'
FROM customers c INNER JOIN customer_info AS i
ON c.id = i.id_customer
GROUP BY i.id_customer
0
user4284772
source
to share
DROP TABLE IF EXISTS eav_hell;
CREATE TABLE eav_hell
(id_customer INT NOT NULL
,attribute VARCHAR(12) NOT NULL
,value VARCHAR(12) NOT NULL
,PRIMARY KEY(id_customer,attribute)
);
INSERT INTO eav_hell VALUES
(1,'name','Doe'),
(1,'age',25),
(1,'city','NY'),
(2,'name','Smith');
SELECT id_customer
, MAX(CASE WHEN attribute = 'name' THEN value END) name
FROM eav_hell
GROUP
BY id_customer;
+-------------+-------+
| id_customer | name |
+-------------+-------+
| 1 | Doe |
| 2 | Smith |
+-------------+-------+
0
source to share