MySQL concatenates multiple lines

Question

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

sql mysql select group-by pivot

Nathan Dec 30. 15 at 13:16

source to share

8 answers

Try the following:

SELECT c.id as 'customer id', info.value as 'Customer Name'
FROM customers c JOIN customer_info info 
ON c.id = info.id_customer

0

Manoj sharma Dec 30. 15 at 13:23

source to share

SELECT id_customer, value FROM customers_info WHERE key LIKE 'name'

0

Yahkem Dec 30. 15 at 13:23

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 Dec 30. '15 at 13:30

source to share

Try this query:

select ci.value
from customers_info ci
INNER JOIN customers c 
    ON c.id = ci.id_customer
WHERE ci.key IN ['name'];

0

deval oza Dec 30. 15 at 13:37

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

Strawberry Dec 30. 15 at 14:06

source to share

select t1.id,t2.key1 from table1 as t1 inner join table2 as t2 on  
t1.id=t2.id_customer group by t2.key1,t1.id  
having t2.key1 in ('name')

SQLFiddle

0

SK2185 Dec 30. 15 at 14:06

source to share

Don't make it too complicated. Just how it goes:

SELECT C.`id`, CI.`value` from customers_info CI
JOIN customers C ON = CI.`id_customer` = C.`id`
WHERE CI.`key` = 'name'
ORDER BY CI.`id_customer`;

0

Ataboy josef Dec 30. 15 at 14:17

source to share

Saharsh shah · Accepted Answer · 2014-12-30T13:18:45+0000

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;

MySQL concatenates multiple lines

More articles: