プログラミング経験がほとんどないのですが、最近mysqlに携わり、検索時のSQL文の記述方法で分からないことがあるので誰か教えてください。
【説明】
地域テーブル、県テーブル、町テーブル、住所テーブル、存在管理テーブルの合計5つのテーブルが存在します。
地域テーブルには地域名と地域コードのカラムがあります。
県テーブルには県名と県コードのカラムがあります。
町テーブルには町テーブルと町コードのカラムがあります。
住所テーブルには地域コード、県コード、町コードと建物の名前のカラムがあります。
存在管理テーブルには、削除フラグというカラムがあります。
ここで削除フラグは、存在しない住所になると削除フラグに1を設定し、検索時には削除フラグに1が立っているものは検索できないようにするためのものです。普段は削除フラグは0になるように設定しています。
地域と県名と町名はプルダウンで選択できるようになっています。
【質問内容】
地域のプルダウンにある「関西」を選択すると選択された地域に該当する県名や町名、建物名が検索される。
また県名、町名についても同様に検索される。
地域、県名に「関西」「大阪」選択すると「関西」「大阪」に該当する町名、建物名が検索される。
さらに建物の名前の前方一致検索もできる。
ちなみにこれを1文で書きたいです。
以上のSQL文はどのようになるでしょうか。
mysqlのバージョンは4.0です。
どなたかお教えください。
No.1ベストアンサー
- 回答日時:
テーブル構成の正規化がうまくいってません。
地域コードとは、複数県をまとめるコードなら、県テーブル側でコードを持たせて結合すべきで、住所テーブルでいちいち設定する形にすると、データ編集時の不整合発見が面倒です。テーブル変更例 ※ カラム型は適当にいれました
地域テーブル (地域名 varchar(255),地域コード varchar(10) primary key);
県テーブル (県名 varchar(255),県コード varchar(10) primary key ,地域コード varchar(10));
町テーブル (町名 varchar(255),町コード varchar(10) primary key ,県コード varchar(10) );
※ 県が違って、町名がかち合っても、町名コードで区別可能にする。同一県内なら名前も区別付くように付けるべき ※
住所テーブル (建物名 varchar(255) , 建物コード varchar(10) primary key, 町コード varchar(10), 削除フラグ byte not null default 0
index indname(建物名) );
※ 存在管理テーブル → これは、住所テーブルのカラムとすべき。 住所テーブルとの連結用カラムもないようでは、管理できないし。町名も合併変更などで使われなくなったのを、削除フラグ管理する場合もそれぞれのテーブルにフラグをもうけるべきと思うが、、、
※ 住所テーブルの県コード、地域コードは、町名コードから連鎖結合で検索可能。同じ町名コードなのに県や地域コードのデータに不整合があっても困る。
建物全件一覧用SQL文
select l.地域名,p.県名,t.町名 , j.建物名 , j.建物コード
from 住所テーブル as j left join 町テーブル as t on j.町コード = t.町コード
left join 県テーブル as p on p.県コード = t.県コード
left join 地域テーブル as l on l.地域コード = p.地域コード
where j.削除フラグ = 0
order by l.地域コード, p.県コード, t.町コード, j.建物名 ;
※ 並び順をコード順にするか名前順(=文字コード順)にするかは、適宜。読みのあいうえお順にしたいなら「ふりがな」カラムがそれぞれのテーブルに必要。
※ あとは、where 条件に and で、必要な条件を付ければ、検索したい情報が選択される。
Q1:地域のプルダウンにある「関西」を選択すると選択された地域に該当する県名や町名、建物名が検索される。
※県名一覧用
select p.県名 from 県テーブル as p
left join 地域テーブル as l on l.地域コード = p.地域コード
where l.地域名 = '関西'
※もしかして建物一覧に出てこない県は表示させたくない?
select distinct p.県名
from 住所テーブル as j left join 町テーブル as t on j.町コード = t.町コード
left join 県テーブル as p on p.県コード = t.県コード
left join 地域テーブル as l on l.地域コード = p.地域コード
where j.削除フラグ = 0 and l.地域名 = '関西'
order by p.県コード
建物一覧 例1
select j.建物名 , j.建物コード, t.町名, p.県名, l.地域名
from 住所テーブル as j left join 町テーブル as t on j.町コード = t.町コード
left join 県テーブル as p on p.県コード = t.県コード
left join 地域テーブル as l on l.地域コード = p.地域コード
where j.削除フラグ = 0 and l.地域名 = '関西'
order by l.地域コード, p.県コード, t町コード, j建物名 ;
Q2また県名、町名についても同様に検索される。
Q3 地域、県名に「関西」「大阪」選択すると「関西」「大阪」に該当する町名、建物名が検索される。
プログラム側で、条件文を適宜作って検索する
→ プログラム側で、条件文を適宜追加して検索する
Q4 さらに建物の名前の前方一致検索もできる。
= 比較ではなく like比較にしてワイルドカードを付ける
説明が下手にもかかわらず、回答ありがとうございました!!
それぞれ、地域、県、町に空白が存在するかのチェックを行い、その条件に従ってSQL文を発行するという形で解釈してもいいでしょうか。
DB設計については、それぞれ地域、県、町でマスメンを考えていたので、住所テーブルにコードを持たせて設計したのですが、マスメンの昨日の設計、プログラミングも時間がかかってしまったので、今後は提案してくださった内容で少しでもメンテナンスが減る設計で考えていきたいです☆
No.2
- 回答日時:
「空白が存在するかのチェック」について
検索用select文は、left join での連結で、連結カラムの空白チェックはしてないので、住所テーブルの町コードが入ってなかったり、町テーブルに存在しないコードだったりすると、全件表示用のSQL文では、地域名、県名、町名すべてnullとして出力されます。検索条件で、地域や県、町を追加指定したときは、null データは検索されません。
よって、住所テーブルデータ登録時には、町コードもその存在をチェックして、未登録であれば、県の指定も求めて、それを町テーブルに登録するような作りが必要です。県と地域は、行数も50行以内と限られてるので、先に全行登録しておくのがよいです。
もしも、県がどの地域に属するかが変更されたときは、県テーブルの地域コードだけ変更すれば、住所テーブルにも即座に反映されます。住所テーブルの複数行を探して編集する必要がなくなるのが正規化の醍醐味。
どういった運用なのか不明ですが、建築計画みたいに町名は決まってないけど、県は決まっているような状況はあり得るのでしょうか?であれば、「町名未確定」といった町名にして、町コードを作っておくなどが良いかと思います。確定後に登録行の編集をするときも未確定で検索し易いかとも思うし。既に存在するものだけなら、町名が決まれば、県も、地域も自動的に決まりますよね。
入力の手間を減らすことを考えると、プログラムコードの方でたくさんやることは増えますが、系統的にプログラミングすると、新規用と編集用とでデータ整合性チェック部分などは少しの変更で使い回しできたりしますので、データ構成を(各コードの決め方も含めて)十分に構想を練ってみてください。
あ、削除フラグカラムのbyte型は、MySQLでは BIT(1) でした。tinyintでも、ハードディスクの記憶容量は変わらないようですけど。フラグデータもカラム型で記憶容量が違ってくるので、何型にするかとか、defaultと、フラグ状態との値を何にするかも、一考してみる価値はあるでしょう。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- MySQL [1000地域 × 10カテゴリー = 1万件のテーブル]!グループ化? 1 2023/06/14 23:56
- Access(アクセス) AccessVBAで降順にするテーブル作成クエリを使用して作成したテーブルを削除し同一のテーブル作成 1 2023/01/06 11:17
- Access(アクセス) AccessVBAで任意の複数リンクテーブルをAccessVBAを動かす際に削除したいと考えておりま 1 2022/11/17 15:45
- MySQL PhpMyAdminで作成して実行せよ。 東京23区を、皇居を中心とした4つのエリア(南東, 南西, 1 2023/06/11 11:58
- MySQL うまくいきません教えてくださいお願いしますSQLです。クエリ構文です。 1 2023/07/07 12:39
- Oracle sql(oracle)で質問です。 テーブルAのカラム名、日付(yyyymmdd)の値を テーブルB 2 2023/01/06 10:31
- MySQL SQLです。こんな感じですか?あってますか? うまくいきません教えてくださいお願いします 1 2023/07/08 15:27
- MySQL 「都道府県の面積の大きい順に並べる」このクエリselect構文を教えてください! テーブル名は'地理 1 2023/07/05 17:11
- Oracle sqlで質問です。 idを元にidに紐付くデータで住所コードがjpのみのデータ以外のidを取得したい 4 2023/03/20 17:41
- Excel(エクセル) PHPプログラムをエクセルに張り付けると検索ボックスがでてくる! 3 2022/05/08 07:10
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【SQL】他テーブルに含まれる値...
-
テーブル名が可変の場合のクエ...
-
データ無し時は空白行にしたい...
-
ACCESSのVBAにてExcelに行...
-
2つのテーブルをLIKE演算子のよ...
-
SQLで、Join句で結合したテ...
-
ExcelのMatch関数のようなもの...
-
同一テーブル内での比較(最新...
-
ADO+ODBCでテーブルに接続する...
-
結合したテーブルをSUMしたい
-
重複データの一方を削除するSQL...
-
三科目合計点のクラス別平均点...
-
テーブルデータの上書きをした...
-
行と列の入れ替え検索(行は可...
-
在庫管理のデータベース
-
ExcelのVLOOKUP関数の動作をMyS...
-
異なるテーブルの値を比較テー...
-
複数のテーブルからデータを取...
-
「都道府県の面積の大きい順に...
-
SQLローダーについて
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【SQL】他テーブルに含まれる値...
-
テーブル名が可変の場合のクエ...
-
結合したテーブルをSUMしたい
-
既存データをINSERT文にして出...
-
SQLで、Join句で結合したテ...
-
データ無し時は空白行にしたい...
-
Accessの構成をコピーしたい
-
ACCESSのVBAにてExcelに行...
-
2つのテーブルをLIKE演算子のよ...
-
SQLサーバに対するSQL文で抽出...
-
ExcelのVLOOKUP関数の動作をMyS...
-
PRIMARY KEYのコピー
-
同一テーブル内での比較(最新...
-
ExcelのMatch関数のようなもの...
-
ADO+ODBCでテーブルに接続する...
-
Access 別のDBに対してDlookup...
-
改行を含んだデータのインポート
-
複数のテーブルからデータを取...
-
ACCESS クエリーでソートの不具合
-
副問合せを使わずにUNIONと同様...
おすすめ情報