SQL文のGROUP_CONCATの絞込み方法の質問です。

Question

お世話になっております。
大変お手数ですがGROUP_CONCATの絞込み方法についてご教授いただけたらと思っております。
下記のようなSQL文があります。

SELECT id,rental_name,city,ward,address,area_id, GROUP_CONCAT(pid ORDER BY pid separator ',') as pids FROM rental LEFT JOIN bukken_kodawari ON id = kid WHERE 1 = 1 AND ((area_id Like "1") OR (area_id Like "2") OR (area_id Like "3")) AND ((pid LIKE "1") OR (pid LIKE "3")) GROUP BY kid

このSQL文だとpidのカラムはOR検索により1,3をどちらかが含むまれた状態で両方表示されます。
id rental_name city ward address area_id pids
6 青葉荘 名古屋市 西区 名古屋市西区名西２丁目 2 1,3
7 名西荘 名古屋市 西区 名古屋市西区名西２丁目 2 1
10 グリーンハイツ 名古屋市 西区 名古屋市西区市場木町 2 3

pidを1,3の両方が存在する箇所のみ表示したい場合、AND検索にする場合どのようにSQL文を入力したら絞込みできるでしょうか？

以下のような結果にしたいです。

id rental_name city ward address area_id pids
6 青葉荘 名古屋市 西区 名古屋市西区名西２丁目 2 1,3

AND ((pid LIKE "1") OR (pid LIKE "3")) を AND ((pid LIKE "1") AND (pid LIKE "3"))
に変更しても絞込み出来ませんでした。
いろいろ自分なりにググってみましたが答えが出てきませんでしたのでどなたか教えていただけたらと思います。
宜しくお願い致します。

###補足しました。

テーブルの定義とは SHOW FULL COLUMNS FROM rental という感じで出せば宜しいのでしょうか？
やったことないのですがとにかく各テーブルの結果を提示します。
よろしくお願いします。

もう一つの以下のSQL文もphpmyadminでは
SHOW CREATE TABLE rental; SHOW CREATE TABLE bukken_kodawari;

このような感じになってしまいます。

なのでDBのデータをダンプしてみます。
（念のため実験なので物件名は架空の物件です）

-- version 4.2.11
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: 2016 年 5 ?E11 日 17:34
-- サーバのバージョン： 5.6.21
-- PHP Version: 5.5.19

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `real_estate`
--

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

--
-- テーブルの構造 `area`
--

CREATE TABLE IF NOT EXISTS `area` (
`area_id` int(10) NOT NULL,
  `ward` varchar(10) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;

--
-- テーブルのデータのダンプ `area`
--

INSERT INTO `area` (`area_id`, `ward`) VALUES
(1, '中村区'),
(2, '西区'),
(3, '昭和区'),
(4, '港区'),
(5, '守山区'),
(6, '東区'),
(7, '中区'),
(8, '熱田区'),
(9, '中川区'),
(10, '千種区'),
(11, '名東区'),
(12, '緑区'),
(13, '天白区'),
(14, '瑞穂区'),
(15, '北区'),
(16, '南区'),
(17, '一宮市'),
(18, '北名古屋市'),
(19, '豊田市');

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

--
-- テーブルの構造 `bukken_kodawari`
--

CREATE TABLE IF NOT EXISTS `bukken_kodawari` (
  `kid` int(11) NOT NULL,
  `pid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- テーブルのデータのダンプ `bukken_kodawari`
--

INSERT INTO `bukken_kodawari` (`kid`, `pid`) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 1),
(3, 2),
(3, 3),
(3, 6),
(4, 3),
(5, 2),
(5, 6),
(6, 1),
(6, 3),
(7, 1),
(7, 6),
(8, 0),
(9, 0),
(10, 2),
(10, 3),
(11, 1),
(12, 2),
(13, 1),
(17, 0);

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

--
-- テーブルの構造 `particular`
--

CREATE TABLE IF NOT EXISTS `particular` (
`hid` int(10) NOT NULL,
  `pid` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- テーブルの構造 `rental`
--

CREATE TABLE IF NOT EXISTS `rental` (
  `id` int(11) NOT NULL DEFAULT '0',
  `area_id` int(10) NOT NULL,
  `rental_name` varchar(100) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `city` varchar(20) DEFAULT NULL,
  `ward` varchar(20) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `memo` text,
  `photofilename` varchar(35) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- テーブルのデータのダンプ `rental`
--

INSERT INTO `rental` (`id`, `area_id`, `rental_name`, `price`, `city`, `ward`, `address`, `memo`, `photofilename`) VALUES
(1, 15, '幸村ビル', 38000, '名古屋市', '北区', '名古屋市北区上飯田南町１丁目', NULL, 'mansion.png'),
(2, 15, 'レグノ 308', 79000, '名古屋市', '北区', '名古屋市北区上飯田南町５丁目', NULL, ''),
(3, 15, 'センター飯田', 80000, '名古屋市', '北区', '名古屋市北区上飯田南町５丁目', NULL, ''),
(4, 15, 'プリミエール 303', 61000, '名古屋市', '北区', '名古屋市北区上飯田南町５丁目', '最寄駅：新宿駅、西新宿駅から徒歩5分', 'primiale303.png'),
(5, 15, 'プリミエール 203', 61000, '名古屋市', '北区', '名古屋市北区上飯田南町５丁目', NULL, ''),
(6, 2, '青葉荘', 25000, '名古屋市', '西区', '名古屋市西区名西２丁目', '最寄駅：新宿駅東口から徒歩9分', 'aobasou.png'),
(7, 2, '名西荘', 25000, '名古屋市', '西区', '名古屋市西区名西２丁目', NULL, ''),
(8, 2, 'レオパレス平田', 30000, '名古屋市', '西区', '名古屋市西区平出町108', NULL, 'leoparess.png'),
(9, 2, 'ダイソー 2階 204', 30000, '名古屋市', '西区', '名古屋市西区城町', NULL, 'daiso.png'),
(10, 2, 'グリーン緑地 1階 103', 32000, '名古屋市', '西区', '名古屋市西区市場木町', NULL, 'greenHeights.png'),
(11, 1, 'ハタエ2階 202', 27000, '名古屋市', '中村区', '名古屋市中村区畑江通１丁目', NULL, 'hatae.png'),
(12, 1, '阿部荘 ', 32000, '名古屋市', '中村区', '名古屋市中村区向島町３丁目15', NULL, 'abesou.png'),
(13, 3, '[賃貸アパート]', 10500, '名古屋市', '昭和区', '愛知県名古屋市昭和区向山町３丁目', NULL, ''),
(14, 4, 'ミリアンアクア', 30000, '名古屋市', '港区', '愛知県名古屋市港区船見町', NULL, 'aquanorth205.png'),
(15, 4, 'アミ当知', 33000, '名古屋市', '港区', '愛知県名古屋市港区当知３丁目', NULL, 'amitytouchi.png'),
(16, 4, 'メゾン 106', 35000, '名古屋市', '港区', '愛知県名古屋市港区当知３丁目', NULL, 'mezondonova106.png'),
(17, 5, 'シーズン山 102', 30000, '名古屋市', '守山区', '愛知県名古屋市守山区更屋敷', NULL, 'seazoncortmoriyama102.png'),
(18, 6, 'ハイツ丘', 33000, '名古屋市', '東区', '愛知県名古屋市東区東大曽根町', NULL, 'heightssakuragaoka.png'),
(19, 6, 'サンハイツ', 33000, '名古屋市', '東区', '愛知県名古屋市東区大幸１丁目', NULL, 'sanheightskoyama.png'),
(20, 6, '徳川アローハイツ 301', 33000, '名古屋市', '東区', '愛知県名古屋市東区徳川１丁目', NULL, ''),
(21, 7, 'ライオンズ名古屋 04010', 20000, '名古屋市', '中区', '愛知県名古屋市中区栄1丁目', NULL, 'lionsnagoyabillding.png'),
(22, 7, '中区丸の内賃貸', 25800, '名古屋市', '中区', '愛知県名古屋市中区丸の内１丁目', NULL, 'marunouchi.png'),
(23, 7, 'ラ・ド・ノーブル 4階 406', 35000, '名古屋市', '中区', '愛知県名古屋市中区新栄１丁目', NULL, 'ladolegidansnoble.png'),
(24, 8, 'プレス宮西 1B', 29000, '名古屋市', '熱田区', '愛知県名古屋市熱田区森後町', NULL, 'buraceinjingunishi1b.png'),
(25, 8, 'マ・メ東海通 104', 32000, '名古屋市', '熱田区', '愛知県名古屋市熱田区五番町', NULL, ''),
(26, 8, 'ドール宮西203', 34000, '名古屋市', '熱田区', '愛知県名古屋市熱田区白鳥３丁目', NULL, ''),
(27, 9, '野荘14', 22000, '名古屋市', '中川区', '愛知県名古屋市中川区野田１丁目', NULL, 'nodasou14.png'),
(28, 9, '憩荘', 22000, '名古屋市', '中川区', '愛知県名古屋市中川区十番町１丁目', NULL, 'ikoisou2.png'),
(29, 10, '千種吹上', 17000, '名古屋市', '千種区', '愛知県名古屋市千種区千種３丁目', NULL, 'hukiagechintai.png'),
(30, 10, 'コーポ大和201', 20000, '名古屋市', '千種区', '愛知県名古屋市千種区城山新町２丁目', NULL, ''),
(31, 10, '川村荘', 25000, '名古屋市', '千種区', '愛知県名古屋市千種区千種３丁目', NULL, 'kawamurasou104.png'),
(32, 11, 'モールＫＦ 4階 4B', 20000, '名古屋市', '名東区', '愛知県名古屋市名東区勢子坊２丁目', NULL, 'mollsezon4f4b.png'),
(33, 11, 'メゾン・ド', 20000, '名古屋市', '名東区', '愛知県名古屋市名東区名東本通４丁目', NULL, ''),
(34, 12, '辰巳荘', 19000, '名古屋市', '緑区', '愛知県名古屋市緑区鳴海町字上汐田', NULL, ''),
(35, 12, 'コーポイジマ', 23000, '名古屋市', '緑区', '愛知県名古屋市緑区黒沢台２丁目', NULL, ''),
(36, 13, 'メゾン・ド', 18000, '名古屋市', '天白区', '愛知県名古屋市天白区井口２丁目', NULL, ''),
(39, 17, 'フォレスタ 104', 23000, '一宮市', '一宮市', '愛知県一宮市向山町１丁目36-2', NULL, '');

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

--
-- テーブルの構造 `t_kodawari_key`
--

CREATE TABLE IF NOT EXISTS `t_kodawari_key` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- テーブルのデータのダンプ `t_kodawari_key`
--

INSERT INTO `t_kodawari_key` (`id`, `name`) VALUES
(1, '新築'),
(2, '南向き'),
(3, 'セキュリティー充実'),
(4, 'システムキッチン'),
(5, 'ペット可'),
(6, '市街地が近い'),
(7, '内装リフォーム');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `area`
--
ALTER TABLE `area`
 ADD PRIMARY KEY (`area_id`);

--
-- Indexes for table `bukken_kodawari`
--
ALTER TABLE `bukken_kodawari`
 ADD UNIQUE KEY `hid` (`kid`,`pid`);

--
-- Indexes for table `particular`
--
ALTER TABLE `particular`
 ADD PRIMARY KEY (`hid`);

--
-- Indexes for table `rental`
--
ALTER TABLE `rental`
 ADD PRIMARY KEY (`id`);

--
-- Indexes for table `t_kodawari_key`
--
ALTER TABLE `t_kodawari_key`
 ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `area`
--
ALTER TABLE `area`
MODIFY `area_id` int(10) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=20;
--
-- AUTO_INCREMENT for table `particular`
--
ALTER TABLE `particular`
MODIFY `hid` int(10) NOT NULL AUTO_INCREMENT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

コード

ご回答いただいた皆様、誠にありがとうございました。
ベスト回答を選ぶのが大変難しかったのですが、解決した順で一番早かった方にさせて頂きました。
本当にありがとうございました。

Comments

[退会済みユーザー]

テーブルの定義を提示してください。

[退会済みユーザー]

SHOW CREATE TABLE rental;
SHOW CREATE TABLE bukken_kodawari; の実行結果を貼り付けしてください。

[pechiper]

テーブル提示はもし機会があれば以後気をつけます。すみませんでした。

[退会済みユーザー]

必要な情報を提示しないと、回答する側も推測をもとに回答せざるをえませんからね。

[pechiper]

ご指摘感謝致します。
丁寧さに欠ける質問でした。

Answer 1

GROUP_CONCAT に distinct を付けて HAVING とかでしょうか。

SELECT id,rental_name,city,ward,address,area_id, GROUP_CONCAT(DISTINCT pid ORDER BY pid separator ',') as pids
FROM rental
  LEFT JOIN bukken_kodawari ON id = kid
WHERE 1 = 1
  AND (
    (area_id Like "1") OR (area_id Like "2") OR (area_id Like "3")
  )
  AND (
    (pid LIKE "1") OR (pid LIKE "3")
  )
GROUP BY kid
HAVING pids = '1,3'

あと、LIKE の部分は honey8823 さんの指摘の鳥 = か IN にまとめるほうが良いです。

---

トリッキーな気もしますが、次のように HAVING でも良さそうでしょうか。

SELECT id,rental_name,city,ward,address,area_id, GROUP_CONCAT(pid ORDER BY pid separator ',') as pids
FROM rental
  LEFT JOIN bukken_kodawari ON id = kid
WHERE 1 = 1
  AND (
    (area_id Like "1") OR (area_id Like "2") OR (area_id Like "3")
  )
  AND (
    (pid LIKE "1") OR (pid LIKE "3")
  )
GROUP BY kid
HAVING SUM(pid = '1') AND SUM(pid = '3')

あと、"1" ではなく '1' の方が良いと思われます。

Comments

[pechiper]

ご回答いただき本当にありがとうございます。
試したところ、上手く動作しました。
本のみの知識ではやはり難しかったので助かりました。

Answer 2

まず本題と関係なくてすみませんが、何故likeを使用しているのでしょう？
「=」での比較やIN句でまとめてしまっても良さそうなものですが…

-- IN句の例
WHERE 1 = 1 AND area_id IN (1,2,3) AND pid IN (1,3)

まあそれはともかくとして、自分であればJOINの部分を変えちゃいますね。
あまりスマートではない気がするので、もっと良い書き方があるかもしれませんが…

テーブル構造に合わせて、適宜調整が必要だと思います。

SELECT id,rental_name,city,ward,address,area_id, GROUP_CONCAT(pid ORDER BY pid separator ',') as pids FROM rental 

INNER JOIN 
  (SELECT kid,COUNT(pid) AS `cnt`
   FROM bukken_kodawari
   WHERE pid IN (1,3)
   GROUP BY kid) AS bukken_kodawari
ON id = kid
AND cnt = 2 -- ここがポイント。1と3の2個だから2

WHERE 1 = 1 AND ((area_id Like "1") OR (area_id Like "2") OR (area_id Like "3")) AND ((pid LIKE "1") OR (pid LIKE "3")) GROUP BY kid

（追記：解説？）

以下のような bukken_kodawari テーブルがあったとします。
－－－－
kid pid
－－－－
1 1
1 4
2 1
2 3
2 4
－－－－

SELECT kid, pid
FROM bukken_kodawari
WHERE pid IN (1,3)

このクエリだと以下のようになりますね。
－－－－
kid pid
－－－－
1 1
2 1
2 3
－－－－

これをkidごとにまとめて、含まれる行数を求めたいのでCOUNTを使います。

SELECT kid,COUNT(pid) AS `cnt`
FROM bukken_kodawari
WHERE pid IN (1,3)
GROUP BY kid

これが、わたしが記載したクエリ(のJOIN部分)ですね。
これだけを実行するとこうなります。
－－－－
kid cnt
－－－－
1 1
2 2
－－－－
1と3の両方をpidに持つkidのレコード数は必ず2になるので、
最初に提示したクエリの、JOINの外でカウントが2のものだけを抽出しているわけです。

Comments

[KiyoshiMotoki]

> まず本題と関係なくてすみませんが、何故likeを使用しているのでしょう？
> 「=」での比較やIN句でまとめてしまっても良さそうなものですが…

万が一(w)、area_id や pid が文字列型で定義されている場合、これには意味があります。

MySQLでは、文字列型を"="（IN句も）で比較する場合、末尾のスペースが無視されてしまうからです。
つまり、MySQLでは
　"hoge" = "hoge "
はtrue、ということです。

一方、LIKE句で比較する場合はこの限りではないからです。

https://dev.mysql.com/doc/refman/5.6/ja/char.html
> MySQL のすべての照合順序は、PADSPACE 型のものです。これは、MySQL 内のすべての CHAR、VARCHAR、および TEXT 値が、末尾のスペースに関係なく比較されることを意味します。このコンテキストでの「比較」には、末尾のスペースが意味を持つ LIKE パターンマッチング演算子は含まれません。

[pechiper]

honey8823様、KiyoshiMotoki様、お答えいただき誠にありがとうございます。

本当に申し訳ないのですが、IN句やLIKEなどあまり深く考えていなかったです。
この場合に限りpidは文字列型ではないので確かにIN句が適切なのかもしれません。

COUNTの使い方がいまいちわからないのでもう少し検証に時間がかかってしまうかもしれません。

今回はより考えさせてくれるご回答いただきありがとうございました。
参考にさせていただきます！

[takushi168]

わたしよりもngyukiさんの回答の方がスマートですが、
一応回答に補足を追記いたしました。

＞KiyoshiMotokiさん
> MySQLでは、文字列型を"="（IN句も）で比較する場合、末尾のスペースが無視されてしまうからです。
> つまり、MySQLでは
> 　"hoge" = "hoge "
> はtrue、ということです。
>
> 一方、LIKE句で比較する場合はこの限りではないからです。

ほ、ほんとだー！ 試してびっくり。これは知らなかったです…
ありがとうございます。勉強になりました。

[pechiper]

honey8823さん、カウントの丁寧な解説、ありがとうございます！
ここまで丁寧な解説は本には書いてませんね。
なんとなく理解できてきました。
COUNTはANDでの絞込み検索には応用が利きそうな感じです。

しかし皆さんの回答が私にはハイレベルな回答過ぎて中々ついていけないのが悩みです。
質問の仕方もテーブルを提示してなかったにもかかわらず、本当感謝です。

Answer 3

こんな風にINNER JOIN句で既に絞られたDBと引き合わせるのはどうですか？

SELECT id,rental_name,city,ward,address,area_id, GROUP_CONCAT(pid ORDER BY pid separator ',') as pids FROM rental LEFT JOIN bukken_kodawari ON id = kid INNER JOIN (SELECT pid FROM rental WHERE pid IN (1,3)) AS r2 ON r2.id= rental.id WHERE 1 = 1 AND ((area_id Like "1") OR (area_id Like "2") OR (area_id Like "3")) AND ((pid LIKE "1") OR (pid LIKE "3")) GROUP BY kid

挿入した文
「INNER JOIN (SELECT pid FROM rental WHERE pid IN (1,3)) AS r2 ON r2.id= rental.id」

Comments

[KiyoshiMotoki]

"rental"テーブルに"pid"というカラムはありませんよ。

[pechiper]

momotaro888様、ご回答頂きありがとうございます。
参考にさせていただきます。

Answer 4

以下で、どうでしょうか？

SELECT 
  r.id,
  r.rental_name,
  r.city,
  r.ward,
  r.address,
  r.area_id, 
  tmp.pids 
FROM rental AS r 
INNER JOIN bukken_kodawari AS k ON r.id = k.kid 
INNER JOIN (
  SELECT kid, GROUP_CONCAT(pid ORDER BY pid SEPARATOR ',') AS pids 
  FROM bukken_kodawari 
  GROUP BY kid
) AS tmp ON r.id = tmp.kid
WHERE r.area_id IN (1, 2, 3)
  AND k.pid IN (1, 3)   # ココのIN句に指定する値の個数と
GROUP BY k.kid 
HAVING COUNT(*) = 2;    # ココの数値を合わせる

上のSQL文にコメントで記載しているように、AND k.pid IN (1, 3)部分の"()"内に指定するpidの個数と
HAVING COUNT(*) = 2部分の数値は一致させるようにしてください。
これが、

pidを1,3の両方が存在する箇所のみ表示

するための肝になっています。

サブクエリの中でGROUP_CONCATしている理由ですが、
例えばpidが"1, 2, 3, 4"と登録されている物件をpid IN (1, 3)で検索した場合、単純にセレクト句の中でGROUP_CONCATしていると、pids = "1,3"となってしまうからです。

Comments

[pechiper]

誠にご丁寧な回答本当にありがとうございます！
動作確認出来ました。
私には難しいのでもう少し基本と照らし合わせて考えたいと思います。
本当にありがとうございました！

Answer 5

各pidが一桁なら、こんな感じでどースかね。

AND ((pid LIKE "%1%") AND (pid LIKE "%3%"))

Comments

[退会済みユーザー]

pid = 13 , pid = 103 にマッチしてしまいますよ。

[takasima20]

pidが二桁以上ならそうなりますね。:-)

[pechiper]

ご回答いただきありがとうございます。
参考にさせていただきます。