Join's вы меня за'join'ли!!

Выборка из 3-х таблиц уникальных значений с группировкой и конкатенацией:

SELECT d.id, d.dealer_name, d.company_name,
p.phoness as phones,
e.emailss as emails
FROM dealers d
LEFT JOIN (SELECT pp.dealer_id,
GROUP_CONCAT(pp.phone SEPARATOR "\n") AS phoness
FROM phones pp
GROUP BY pp.dealer_id) p ON d.id = p.dealer_id
LEFT JOIN (SELECT ee.dealer_id,
GROUP_CONCAT(ee.email SEPARATOR "\n") AS emailss
FROM emails ee
GROUP BY ee.dealer_id) e ON d.id = e.dealer_id
WHERE d.hot = 0
AND d.city_id IN (
SELECT id
FROM cities
WHERE country_id =2
)

2-й способ

SELECT d.dealer_name, d.company_name,
p.phoness as phones,
COALESCE(NULLIF(e.emailss ,NULL), '') as emails
FROM dealers d
LEFT JOIN (SELECT pp.dealer_id,
GROUP_CONCAT(CONCAT('38', pp.phone) SEPARATOR '\n') AS phoness
FROM phones pp
GROUP BY pp.dealer_id) p ON d.id = p.dealer_id
LEFT JOIN (SELECT ee.dealer_id,
GROUP_CONCAT(ee.email SEPARATOR '\n') AS emailss
FROM emails ee
GROUP BY ee.dealer_id) e ON d.id = e.dealer_id
WHERE d.hot = 0
AND d.city_id IN (
SELECT id
FROM cities
WHERE country_id =2
)

3-й способ самый крутой в выгрузкой сразу в csv

SELECT d.dealer_name, d.company_name, p.phoness as phones, COALESCE(NULLIF(e.emailss ,NULL), '') as emails 
INTO OUTFILE "/backup/books/allbooks.csv" 
FIELDS TERMINATED BY ';' 
OPTIONALLY ESCEPED BY '"'
LINES TERMINATED BY '\n' 
FROM dealers d
LEFT JOIN (SELECT pp.dealer_id, GROUP_CONCAT(CONCAT('38', pp.phone) SEPARATOR '\n') AS phoness FROM phones pp  GROUP BY pp.dealer_id) p ON d.id = p.dealer_id 
LEFT JOIN (SELECT ee.dealer_id, GROUP_CONCAT(ee.email SEPARATOR '\n') AS emailss FROM emails ee  GROUP BY ee.dealer_id) e ON d.id = e.dealer_id  
WHERE d.hot = 0 AND d.city_id IN (SELECT id FROM cities WHERE country_id = 2)

 

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *