SQL文について質問させて頂きます。

質問

いつもお世話になっております。いろいろSQL文の本を読んだのですがわからないので今回もよろしく
お願い致します。

SELECT DISTINCT * FROM bukken WHERE (id IN (SELECT fid FROM fudou_kodawari WHERE kid IN (1,4)))

というSQL文があるのですが、これだと1,4が1つでも含まれるリストが出てくるのですが1,4が完全に一致しないと出てこないようにするにはどのようにSQL文を変更すれば良いのでしょうか？

何卒よろしくお願い致します。

回答No.3

yambejp

#2のお礼欄にある例を使えば

create table bukken(id int, bukken_name varchar(30), chiiki_id int, address varchar(200), price_min int, price_max int, station varchar(30));
insert into bukken values(1,'名駅MID',1,'愛知県名古屋市中村区則武本通x丁目xx番',40770000,55100000,'名古屋駅'),(2,'プレサン浄心',2,'愛知県名古屋市西区城西x丁目xxxx番x',30800000,40001000,'浄心駅'),(3,'アネシア八事',3,'愛知県名古屋市昭和区高峯町xxx番',50000000,62510000,'八事日赤');

create table fudou_kodawari (fid int not null,kid int not null,unique(fid,kid));
insert into fudou_kodawari values(1,1),(1,2),(1,3),(2,1),(3,2),(3,3),(3,4),(4,3),(5,4),(6,4),(7,1),(8,1),(8,2),(8,4),(9,5),(10,1),(10,4);

として、fudou_kodawariに1かつ4を持つfidは8と10になります。
bukkenテーブルにidの8,10がないためヒットするレコードは一つも表示されません。
bukkennテーブルに該当レコードを入れたうえで再チャレンジすればいけると思いますよ

SELECT * FROM bukken WHERE id IN (select fid from fudou_kodawari where kid in (1,4) group by fid having count(*)=2);

サブクエリ部分の考え方

(1)select fid from fudou_kodawari where kid in (1,4)
とすると1か4のレコードが表示される

(2)select fid,count(*) from fudou_kodawari where kid in (1,4) group by fid
とすると1か4のレコードがfidごとにいくつあるか表示される

(3)select fid from fudou_kodawari where kid in (1,4) group by fid having count(*)=2
1か4でカウントされた個数2・・・つまり1かつ4のレコードが抽出される

という流れです。

これをbukkenのidに対してINで抽出するわけですが、bukkennはレコードが被ることはないので
bukkenに対するDISTINCTは不要です。

お礼

回答いただきありがとうございます。
頼まれ事ができましたので少し検証に時間が掛かるかもしれませんのでまた返事させていただきます。

補足

いつも回答いただきありがとうございます。
以前の続きですが、かなり間が空いてしまいましたが、もう一度ご教授いただければと思っております。
よろしくお願い致します。

PHPMyadminで試したところ1かつ4の値の持っているデータが表示されました。
ありがとうございます。

SELECT * FROM bukken WHERE id IN (select fid from fudou_kodawari where kid in (1,4) group by fid having count(*)=2);
というSQL文をどのように下記のようにPHPで値を受けるように表記すれば良いのでしょうか？

上記のSQL文のWHERE以降のid INを

kidの絞込みの
if(isset($_REQUEST["kid"]) and is_array($_REQUEST["kid"])){
$sql. =　ここから文を打ち込めばよいのでしょうか？
その時どのようにつなげていけば良いのでしょうか？

お手数ですが回答頂けたらと思っております。
何卒よろしくお願い致します。

$sql = "SELECT t1.* FROM bukken as t1 ";

$flag=true;

//名古屋の地域checkbox

if(isset($_REQUEST["chiiki_id"]) and is_array($_REQUEST["chiiki_id"])){
$sql.="INNER JOIN (SELECT DISTINCT chiid FROM chiiki WHERE chiiki_id in (";
$sql.=implode(",",array_fill(0,count($_REQUEST["chiiki_id"]), '?'));
$sql.=")) as t2 ON t1.id=t2.chiid ";
$data=array_merge($data,$_REQUEST["chiiki_id"]);
$flag=false;
}

//kid絞り込み

if(isset($_REQUEST["kid"]) and is_array($_REQUEST["kid"])){
$sql.="INNER JOIN (SELECT DISTINCT fid FROM fudou_kodawari WHERE kid in (";
$sql.=implode(",",array_fill(0,count($_REQUEST["kid"]), '?'));
$sql.=")) as t2 ON t1.id=t2.fid ";
$data=array_merge($data,$_REQUEST["kid"]);
$flag=false;
}

回答No.2

yambejp

ちなみに・・・

fidとkidの組み合わせがユニークな場合
create table f1 (fid int not null,kid int not null,unique(fid,kid));
insert into f1 values(1,1),(1,2),(1,3),(2,1),(2,3),(2,4),(3,1);
select fid from f1 where kid in (1,4) group by fid having count(*)=2;
という感じです。（この場合fid=2だけ抽出される）

fidとkidの組み合わせがユニークでない場合は
create table f2 (fid int not null,kid int not null);
insert into f2 values(1,1),(1,1),(1,2),(1,3),(2,1),(2,3),(2,4),(3,1),(3,1),(3,1),(3,4);
select fid from f2 where kid in (1,4) group by fid having count(distinct kid)=2;
のようになります。（この場合2と3が抽出される）

f1の例のまま
select fid from f2 where kid in (1,4) group by fid having count(*)=2
をすると、1と2が抽出されて明らかにおかしい。
fid=1はkid=1を2個もっているので合致、
fid=3はkid=1が3個あるので合致しない

お礼

yambejp様、いつもありがとうございます！

いろいろな質問サイトでも聞いている状態なのですが、私の説明が不十分なので解決しないままなので今回もこちらにお世話になります。
fudou_kodawariテーブルはユニークになっております。
なので確かに考えてみればkidの1,4にチェックが入った場合は完全に一致するものだけ表示するのはできないみたいですね。
となるとテーブルの構造自体を見直さなければkidの範囲内でのandで絞込み検索は出来ないですね。
どのようなテーブルの構造にすれば実現できるのでしょうか？
あとはkidのor検索とand検索を切り替えられたら一番良いと思っておりますができますでしょうか？

SQL文の問題だと思っていたのですがテーブルの構造に問題あるみたいなのでもっと最初から詳しく説明するべきでした。

yambejp様に以前教えて頂いたcommon.phpとtest.inc.htm、test.phpなのですが続きはございますでしょうか？
SQLの分のファイルはどうなるのかは正直気になってしまいます。

気が乗らないのでしたら全く進めなくても全然大丈夫ですが気になったのもので。

//フォーム

<table>
<tr>
<th>こだわり条件</th>

<td>
<input type="checkbox" name="kid[]" value="1" <?php if (isset($_REQUEST['kid']) and in_array('1',$_REQUEST['kid'])) print 'checked'; ?>/>
新築
</tr>
</table>

$sql = 'SELECT DISTINCT * FROM bukken';

//名古屋の地域checkbox

if(isset($_POST['chiiki_id'])and is_array($_POST['chiiki_id'])){
$ary = array_filter($_POST['chiiki_id'], function($v) {return is_numeric($v);});
$sqlA = 'id IN (SELECT chiid FROM chiiki WHERE chiiki_id IN ('.implode(',', $ary).'))';
}else{ //print '地域がチェックされてません';
$sqlA = ' 1 ';

//kid絞り込み

if(isset($_POST['kid'])and is_array($_POST['kid'])){
$ary = array_filter($_POST['kid'], function($v) {return is_numeric($v);});
$sqlK = 'id IN (SELECT fid FROM fudou_kodawari WHERE kid IN ('.implode(',', $ary).'))';
}else{ //print '<br />こだわりがチェックされてません';
$sqlK = ' 1 ';
}

$sql.=' WHERE ('.$sqlK.')'; // こだわり条件
$sql.=' AND ('.$sqlA.')'; // エリア

以下省略

テーブルbukken

id bukken_name chiiki_id address price_min price_max station
1 名駅MID 1 愛知県名古屋市中村区則武本通x丁目xx番 40770000 55100000 名古屋駅
2 プレサン浄心 2 愛知県名古屋市西区城西x丁目xxxx番x 30800000 40001000 浄心駅
3 アネシア八事 3 愛知県名古屋市昭和区高峯町xxx番 50000000 62510000 八事日赤

テーブルchiiki
chiid chiiki_id
1 1
2 2
3 3
4 4
5 15

テーブルfudou_kodawari
fid kid
1 1
1 2
1 3
2 1
3 2
3 3
3 4
4 3
5 4
6 4
7 1
8 1
8 2
8 4
9 5
10 1
10 4

回答No.1

yambejp

まずはfudou_kodawariの構造次第ですね

fidがユニーク属性であれば、kidが1か4であるときfidは必ず別の値になるので
「1,4が完全に一致」することは絶対にないです。
fidがユニークではなく、kidはユニークなのでしょうか？
fidがユニークでなく、kidもユニークでなく、fidとkidの組み合わせがユニークなのでしょうか？
もしくはそのテーブルにユニークな属性はないのでしょうか？

それぞれによって微妙にSQL文がかわってくると思います