複数のテーブルに渡る検索を行いたい

Question

PHPを使った検索フォームから入力された値を使ってMySQLでデータベースからデータを取ってくるということをしたいのですが、少し複雑なところがあり、また未熟者でもあるのでお力を貸していただければと思い質問させていただきます。
データベース(以下DB)のテーブルが5つあり、テーブルの名前がA、B-1、B-2、B-3、B-4とします。
AのDBの一つのカラムにtable_typeとして1，2，3，4の数字が入り、1がB-1、2がB-2、3がB-3、4がB-4に対応しています。
また別のカラムにtitle_idとしてBのいずれかのテーブルの中のidが入っています。
Bのテーブルにはカラムとしてint型のidとtext型のタイトルがあります。
例えば
Aのtable_typeが2でtitle_idが23のとき、B-2のidが23のところに対応しています。そこからタイトルを知ることができます。
今回やりたいことはまず入力された値を4つのBのテーブルのタイトルの中から部分一致で当てはまる箇所を探してきてそこからAのtable_typeとtitle_idを取ってくるという動作をしたいです。

入力された値が「ああ」の場合
もしB-2のidが3の箇所のタイトルのところに「ああいいうう」というデータが入っていたならテーブルAのtable_typeが2でtitle_idが3である部分の行を取り出したいとします。

create table A (id int, table_type tiny_int(4), title_id int);
create table B-1 (id int, title varchar(10));
create table B-2 (id int, title varchar(10));
create table B-3 (id int, title varchar(10));
create table B-4 (id int, title varchar(10));

INSERT INTO A(id,table_type,title_id)VALUES(1,1,3);
INSERT INTO A(id,table_type,title_id)VALUES(2,4,4);
INSERT INTO A(id,table_type,title_id)VALUES(3,2,3);
INSERT INTO A(id,table_type,title_id)VALUES(4,3,1);

INSERT INTO B-1(id,table_type,title_id)VALUES(1,'ttt');
INSERT INTO B-1(id,table_type,title_id)VALUES(2,'aaa');
INSERT INTO B-1(id,table_type,title_id)VALUES(3,'yyy');

INSERT INTO B-2(id,table_type,title_id)VALUES(1,'あかさたな');
INSERT INTO B-2(id,table_type,title_id)VALUES(2,'あいうえお');
INSERT INTO B-2(id,table_type,title_id)VALUES(3,'ああいいうう');

INSERT INTO B-3(id,table_type,title_id)VALUES(1,'111');
INSERT INTO B-3(id,table_type,title_id)VALUES(2,'222');
INSERT INTO B-3(id,table_type,title_id)VALUES(3,'333');

INSERT INTO B-4(id,table_type,title_id)VALUES(1,'ppp');
INSERT INTO B-4(id,table_type,title_id)VALUES(2,'qqq');
INSERT INTO B-4(id,table_type,title_id)VALUES(3,'rrr');

またテーブルAに入っているデータがテーブルBに入っていないということはありません。
この場合どのようにすればよいでしょうか？
また至らぬところがありましたらご指摘お願いします。

Comments

[Orlofsky]

SQLの動作確認が必要な場合は、質問に CREATE TABLE と INSERT を https://teratail.com/help#about-markdown の[コードを入力] で載せると適切な回答が付き易いです。

[Orlofsky]

>Aのtable_typeが2でtitle_idが23のとき、B-2のidが23のところに対応しています。
初歩的なことですが、説明文に載っているサンプルデータを提示しましょう。

SQL では - ハイフンはマイナスの意味で使うので、通常テーブル名や列名には使いません。バックスラッシュでテーブル名や列名を囲めば使えたかもですが、良識がある経験者が入る度にどうしてこんな変なネーミングにしたの？って質問され続けられます。

テーブル定義に PRIMARY KEYやINDEXを追加してください。

Answer 1

Aのtable_typeの値で連結するテーブルが違うということですか？
それはやめたほうがよいでしょう
B1～B4をひとつのテーブルBにして1～4の情報をカラムでもてば解決します

どうしても運用方法が変えられないなら、プロシージャで場合分けするのが妥当でしょう

Comments

[yambejp]

状況がわからないので、どうしてもというなら
具体的なテーブルのサンプルをつけてください

Answer 2

create table A (id int, table_type tinyint(4), title_id int);
create table B_1 (id int, title varchar(10));
create table B_2 (id int, title varchar(10));
create table B_3 (id int, title varchar(10));
create table B_4 (id int, title varchar(10));

INSERT INTO A(id,table_type,title_id)VALUES(1,1,3);
INSERT INTO A(id,table_type,title_id)VALUES(2,4,4);
INSERT INTO A(id,table_type,title_id)VALUES(3,2,3);
INSERT INTO A(id,table_type,title_id)VALUES(4,3,1);

INSERT INTO B_1 VALUES(1,"ttt");
INSERT INTO B_1 VALUES(2,"aaa");
INSERT INTO B_1 VALUES(3,"yyy");

INSERT INTO B_2 VALUES(1,"あかさたな");
INSERT INTO B_2 VALUES(2,"あいうえお");
INSERT INTO B_2 VALUES(3,"ああいいうう");

INSERT INTO B_3 VALUES(1,"111");
INSERT INTO B_3 VALUES(2,"222");
INSERT INTO B_3 VALUES(3,"333");

INSERT INTO B_4 VALUES(1,"ppp");
INSERT INTO B_4 VALUES(2,"qqq");
INSERT INTO B_4 VALUES(3,"rrr");

SELECT	A.*
	,	ifnull( B_1.title, ifnull( B_2.title, ifnull( B_3.title, ifnull( B_4.title, '' ) ) ) ) as title
FROM	A
			LEFT JOIN B_1 ON (
					1				= A.table_type
				AND	B_1.id			= A.title_id
			)
			LEFT JOIN B_2 ON (
					2				= A.table_type
				AND	B_2.id			= A.title_id
			)
			LEFT JOIN B_3 ON (
					3				= A.table_type
				AND	B_3.id			= A.title_id
			)
			LEFT JOIN B_4 ON (
					4				= A.table_type
				AND	B_4.id			= A.title_id
			)
where	ifnull( B_1.title, ifnull( B_2.title, ifnull( B_3.title, ifnull( B_4.title, '' ) ) ) ) like '%ああ%'
;

B_1～B_4にはそれぞれ中身が1～4に固定されているカラムがあるイメージですね。

Answer 3

SELECT A.table_type, A.title_id
FROM　A
LEFT JOIN(
    SELECT
        1 AS TABLE_TYPE,
        ID,
        TITLE
    FROM
        B-1
    UNION ALL
    SELECT
        2 AS TABLE_TYPE,
        ID,
        TITLE
    FROM
        B-2
    UNION ALL
    SELECT
        3 AS TABLE_TYPE,
        ID,
        TITLE
    FROM
        B-3
    UNION ALL
    SELECT
        4 AS TABLE_TYPE,
        ID,
        TITLE
    FROM
        B-4
)B
ON A.table_type = B.TABLE_TYPE 
AND A.title_id = B.ID
WHERE B.TITLE LIKE '%あああ%'

ちょっと無理矢理感がありますが
BテーブルのIDとTITLE列の型が等しいのが前提です。
Ｂテーブルをすべて結合してAテーブルとジョインしてから条件で絞りこむイメージです
結合するさいにどのテーブルか識別するためにTABLE_TYPEを追加しています
ただ、このテーブルレイアウトだとAテーブルにTITLEを持てばいいだけな気もします

Answer 4

yambejp さんの書いているように、テーブル設計を見直したほうが良いかと思います。

B1〜4をBテーブルとして、種類をカラムで管理します。
B1〜4でカラム数などが違うとしても、全てのカラムを用意して、1〜4で入力するカラムを変更すれば良いです。

そうすると、以下で取得できると思います。

SELECT * FROM A INNER JOIN B ON A.table_type = B.table_type WHERE A.table_type = 1 AND B.title like '%ああ%';