SQL文、検索の仕方について

Question

質問内容

　皆さま、こんにちわ。私は今以下の様な条件で検索できるSQL分はないかと探しています。

table: estate

{
  id: 1,
  name: サンプル建設
},
{
  id: 2,
  name: サンプル不動産
}

table: tags
{
  id: 1,
  tag: 神奈川県,
  estate_id: 1
},
{
  id: 2,
  tag:ペット可,
  estate_id: 1
},
{
  id: 3,
  tag:　東京都,
  estate_id: 2
}

この様なテーブル構造で「神奈川県タグとペット可タグをもつ不動産」を検索できるSQL文はないでしょうか？皆様のお知恵をお借りできれば幸いです。

また、質問者自身RDBに詳しくないのでテーブル構造がそもそもまずいということもあると思います。ですのでもっと良いテーブル構造があればご教授いただければ幸いです。よろしくお願いします。

Answer 1

不動産屋さんには複数のタグが付くかもしれないし、同じタグが複数の不動産屋さんにつくかもしれませんよね？
そんな場合は中間テーブルを使って、多対多結合を表現するのがベストプラクティスです。

多対多結合ついて詳しくはググってください。

以下の3つのテーブルがあったとして

estatesテーブル
id(主キー)
name(ユニーク)

tagsテーブル
id (主キー)
name(ユニーク)

estates_tagsテーブル
tag_id(主キー)
estate_id(主キー)

SQLはこんな感じで

SELECT estates.name
FROM estates
INNER JOIN estates_tags
  ON estates_tags.estate_id = estates.id
INNER JOIN tags
  ON estates_tags.tag_id = tags.id
WHERE tags.name = "神奈川県" OR tags.name = "ペット可"
GROUP BY estates.name
HAVING COUNT(0) = 2

テーブルも追記します。

CREATE TABLE estates(id int PRIMARY KEY, name varchar(255) UNIQUE);

CREATE TABLE tags(id int PRIMARY KEY, name varchar(255) UNIQUE);

CREATE TABLE estates_tags(
  estate_id int NOT NULL,
  tag_id int NOT NULL,
  PRIMARY KEY(estate_id, tag_id),
  FOREIGN KEY(estate_id) REFERENCES estates(id),
  FOREIGN KEY(tag_id) REFERENCES tags(id));

INSERT INTO estates (id, name)
  VALUES (1, 'サンプル建設'), (2, 'サンプル不動産');

INSERT INTO tags (id, name)
  VALUES (1, '神奈川県'), (2, 'ペット可'), (3, '東京都');

INSERT INTO estates_tags(estate_id, tag_id)
VALUES (1, 1), (1, 2), (2, 3), (2, 2)

Comments

[sueken5]

非常にためになる回答ありがとうございます。今回ベストアンサーとさせていただきました。今後ともよろしくお願いします。

Answer 2

一応、でけました。

###お膳立て

create database test CHARACTER SET utf8mb4;

use test;

create table estate (
id int PRIMARY KEY,
name varchar(255)
) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

create table tags(
id int PRIMARY KEY,
tag varchar(255),
estate_id int
) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

INSERT INTO test.estate (id, name) VALUES (1, 'サンプル建設'), (2, 'サンプル不動産');
INSERT INTO test.tags (id, tag, estate_id) VALUES (1, '神奈川県', 1), (2, 'ペット可', 1), (3, '東京都', 2);

###SELECTクエリー

SELECT e.id, e.name, t.ts
FROM estate AS e
LEFT JOIN (SELECT estate_id, group_concat(tag) AS ts FROM tags GROUP BY estate_id ORDER BY id ASC) AS t
ON e.id = t.estate_id
WHERE t.ts LIKE '%神奈川%' AND t.ts LIKE '%ペット可%';

###SELCT結果

Comments

[sueken5]

実際の例まで出してくれて本当にありがとうございます。しかし今回は多対多結合を利用するという一般的に採用されている例を提示してくれている方がいらっしゃったのでそちらの方をベストアンサーとさせていただきます。何卒ご了承ください。

Answer 3

「神奈川県タグとペット可タグ」これは、「所在地」と「ペット可否」という独立した情報なので、「タグ」という一括りの構造にはしない方が良いです。

独立していればAND条件も容易になります。

当然登録する際の画面を考えると、単にタグの欄に列挙されるだけだと扱いづらいですし。

Comments

[sueken5]

回答ありがとうございました。とてもためになりました。

Answer 4

これでよかったような…。

SELECT e.name 
FROM estate AS e 
LEFT JOIN tags AS t 
  ON e.id = t.estate_id 
WHERE t.tag = '神奈川県' OR t.tag = 'ペット可' 
GROUP BY e.id 
ORDER BY e.id ASC;

Comments

[退会済みユーザー]

神奈川県タグとペット可タグをもつ　←　これは、神奈川県タグまたはペット可タグをもつ
かな？

[sueken5]

コメントありがとうございます。
両方がいいです。その場合はORの部分をANDにしたらいいということでしょうか？

[退会済みユーザー]

そうすると、結構変わってきちゃうと思います。時間があったらあとでレスします。