[EC-CUBE] 2.13.5
[レンタルサーバ] WADAX
[OS] win7
[PHP] 5.3.3
[データベース] MySQL 5.1.73
[WEBサーバ] Apache

いつもお世話になっております。
CSV高度な設定　配送先を含んだ受注データの配送先県ですが、IDではなくて県名で出力したいのですが、どのように書けばよいのでしょうか。

注文者情報の件はIDではなく数字で表示できたのですが、、

dtb_order.customer_id,
dtb_order.order_id as "注文番号",
dtb_order_detail.order_detail_id,
dtb_order.create_date,
(SELECT count(*) FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id),
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_id FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_id",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_name01 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_name01",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_name02 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_name02",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_kana01 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')),
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_kana02 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_kana01",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_zip01 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_zip01",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_zip02 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_zip02",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_pref FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "県名",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_addr01 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_addr01",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_addr02 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_addr02",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_tel01 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_tel01",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_tel02 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_tel02",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_tel03 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_tel03",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_fax01 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_fax01",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_fax02 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_fax02",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_fax03 FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_fax03",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_date FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_date",
(SELECT array_to_string(array(SELECT dtb_shipping.shipping_time FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "shipping_time",
dtb_order.payment_total,
dtb_order.payment_method,
dtb_order.deliv_fee,
dtb_order.charge,
dtb_order.use_point,
dtb_order.add_point,
dtb_order.device_type_id,
dtb_order.status,
dtb_order.order_name01,
dtb_order.order_name02,
dtb_order.order_kana01,
dtb_order.order_kana02,
dtb_order.order_sex,
dtb_order.order_birth,
dtb_order.order_email,
dtb_order.order_tel01,
dtb_order.order_tel02,
dtb_order.order_tel03,
dtb_order.order_zip01,
dtb_order.order_zip02,
(SELECT mtb_pref.name FROM mtb_pref WHERE mtb_pref.id = dtb_order.order_pref),
dtb_order.order_addr01,
dtb_order.order_addr02,
dtb_order.message,
dtb_order_detail.product_name,
dtb_order_detail.product_id,
dtb_order_detail.product_code,
dtb_order_detail.classcategory_name1,
dtb_order_detail.classcategory_name2,
dtb_order_detail.price,
dtb_order_detail.quantity,
dtb_order.total,
dtb_order.tax,
dtb_order.subtotal,
dtb_order.discount,
dtb_order.note,
dtb_order.update_date,
dtb_order.commit_date

FROM
dtb_order
INNER JOIN
dtb_order_detail
ON
dtb_order.order_id = dtb_order_detail.order_id
WHERE
del_flg = 0
AND dtb_order.create_date BETWEEN '2017-12-28 00:00:00' AND '2020-12-31 23:59:59'

(SELECT array_to_string(array(SELECT dtb_shipping.shipping_pref FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ',')) as "県名",

の箇所なのですが、どうしてもわかりません。
ご教授いただけると幸いです。
宜しくお願い致します。

dtb_shipping.shipping_pref の部分を
(SELECT name FROM mtb_pref WHERE id = dtb_shipping.shipping_pref)
に変更すればできると思います。

----------------


http://ec-lab.net/

返答が遅くなり申し訳ありません。
色々試したのですが、構文エラーになってしまいます。

(SELECT array_to_string(array(SELECT (SELECT mtb_pref.name FROM mtb_pref WHERE mtb_pref.id = dtb_shipping.shipping_pref) FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ','))

【エラー内容】

[Native message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SEPARATOR ',') FROM mtb_pref WHERE mtb_pref.id = dtb_shipping.shipping_pref) FR' at line 1]


間違っている箇所がわかりません。
ご教授いただけますと幸いです。
宜しくお願いします。