MySQL ляво присъединяване с group_concat

Имам SQL Fiddle със създаването на таблица и заявката, с която имам проблеми: http://www.sqlfiddle.com/#!9/3404e/1

Ето скрипта за създаване на таблица:

CREATE TABLE IF NOT EXISTS `collection` (
  `id` bigint(20) unsigned NOT NULL,
  `name` varchar(50) NOT NULL,
  `label` varchar(120) NOT NULL,
  `label_plural` varchar(120) NOT NULL);

INSERT INTO `collection` (`id`, `name`, `label`, `label_plural`) VALUES
    (1, 'account', 'Account', 'Accounts');

CREATE TABLE IF NOT EXISTS `field` (
  `id` bigint(20) unsigned NOT NULL,
  `name` varchar(50) NOT NULL,
  `label` varchar(120) NOT NULL,
  `collection_id` bigint(20) unsigned NOT NULL);

INSERT INTO `field` (`id`, `name`, `label`, `collection_id`) VALUES
    (1, 'name', 'Name', 1),
    (2, 'state', 'State', 1);

CREATE TABLE IF NOT EXISTS `option` (
  `id` bigint(20) unsigned NOT NULL,
  `record_type_id` bigint(20) unsigned DEFAULT NULL,
  `field_id` bigint(20) unsigned NOT NULL,
  `value` varchar(120) NOT NULL);

INSERT INTO `option` (`id`, `record_type_id`, `field_id`, `value`) VALUES
    (1, NULL, 2, 'CO'),
    (2, NULL, 2, 'NE'),
    (3, NULL, 2, 'BC'),
    (4, NULL, 2, 'MB'),
    (5, 1, 2, 'CO'),
    (6, 1, 2, 'NE'),
    (7, 2, 2, 'BC'),
    (8, 2, 2, 'MB');

CREATE TABLE IF NOT EXISTS `record_type` (
  `id` bigint(20) unsigned NOT NULL,
  `name` varchar(120) NOT NULL,
  `collection_id` bigint(20) unsigned NOT NULL);

INSERT INTO `record_type` (`id`, `name`, `collection_id`) VALUES
    (1, 'US', 1),
    (2, 'Canada', 1);

Ето заявката, която се опитвам да изпълня:

select
    `field`.`name`,
    `field`.`label`,
    ifnull(group_concat(`option`.`value` separator ';'), '') as `options`
from
    `field`
join
    `collection` on
        `collection`.`id` = `field`.`collection_id`
join
    `record_type` on
        `record_type`.`collection_id` = `collection`.`id`
left join
    `option` on
        `option`.`record_type_id` = `record_type`.`id` and
        `option`.`field_id` = `field`.`id`
where
    `record_type`.`name` = 'US' and
    `collection`.`name` = 'account';

Това, което очаквам, са два реда, както следва:

+-------+-------+---------+
| name  | label | options |
+-------+-------+---------+
| name  | Name  | NULL    |
| state | State | CO;NE   |
+-------+-------+---------+

Но аз получавам само държавния ред. Ако премахна реда group_concat, получавам трите реда, както следва, така че знам, че всичко се връща:

+-------+-------+--------+
| name  | label | option |
+-------+-------+--------+
| name  | Name  | NULL   |
| state | State | CO     |
| state | State | NE     |
+-------+-------+--------+

mysql left-join group-concat
person travis.paxton    schedule 22.08.2013    source източник

Отговори (2)


arrow_upward
4
arrow_downward

По същество ви липсва GROUP BY, GROUP_CONCAT() обединява всички стойности в група и без GROUP BY има само една група.

Опитайте тази;

select
    `field`.`name`, `field`.`label`,
    group_concat(`option`.`value` separator ';') as `options`
from `field`
join `collection` 
  on `collection`.`id` = `field`.`collection_id`
join `record_type` 
  on `record_type`.`collection_id` = `collection`.`id`
left join `option` 
  on `option`.`record_type_id` = `record_type`.`id` 
 and `option`.`field_id` = `field`.`id`
where `record_type`.`name` = 'US' 
  and `collection`.`name` = 'account'
GROUP BY `field`.`name`,`field`.`label`

SQLfiddle с корекцията.

person Joachim Isaksson    schedule 22.08.2013

arrow_upward
0
arrow_downward

Основният проблем с вашата заявка е, че нямате group by клауза.

В допълнение вашите where условия "отменят" left joins, превръщайки ги в inner joins. Следното постига това, което искате:

select
    `field`.`name`,
    `field`.`label`,
    ifnull(group_concat(`option`.`value` separator ';'), '') as `options`
from
    `field`
join
    `collection` on
        `collection`.`id` = `field`.`collection_id` and
        `collection`.`name` = 'account'
join
    `record_type` on
        `record_type`.`collection_id` = `collection`.`id` and
        `record_type`.`name` = 'US' 
left join
    `option` on
        `option`.`record_type_id` = `record_type`.`id` and
        `option`.`field_id` = `field`.`id`
group by field.name, field.label;
person Gordon Linoff    schedule 22.08.2013