SQLite3データベースを使用した結合についての質問

前へ 次へ
このQ&Aのポイント
  • SQLite3データベースを使用している場合、2つのテーブルt1とt2を結合してt1.idに対してグループ化を行い、山田花子さんはアメリカへ行ったことがあることを取得する方法について質問です。
  • t2に山田太郎君の項目がなくてもNULLとして取得したい場合、どのようにすれば良いか教えてください。
  • 以下は欲しい表の形です。t1.id, t1.名前, t2.国のカラムを持ち、山田花子さんの場合はt2テーブルと結合してアメリカに行ったことがあることを示し、山田太郎さんの場合はt2テーブルがないため、結合しても取得できないことを示しています。
回答を見る
  • ベストアンサー

結合について質問です

SQLite3データベースを使っています。 2つのテーブルt1とt2があり、t2はt1の重複する部分を担っています。 t2はt1に対し、NULLでもよい項目です。 t1には主キーのidと名前、 t2には主キーのidと、t1と結びつけるt1.id（外部キーにはしていません）と、 行ったことのある国の3つがあります。 山田花子さんはアメリカとイギリスに行ったことがあり、 山田太郎君はどこへも行ったことがないとします。 この時、行ったことのある国を1つ選ぶ処理をしたいのですが、 t1とt2を結合して、t1.idに対しグループ化（group by）を行った場合、 山田花子さんはアメリカへ行ったことがあります。 となります。 t2に山田太郎君の項目がない為、結合しても山田太郎君は取得できません。 もしt2に項目がなかった場合でも、 NULLとして取得したいのですがどうすればよいでしょうか？ こういう表が欲しいです t1.id　t1.名前　　 t2.国 1　　 山田花子　アメリカ 2　　 山田太郎 create table t1 (t1.id integer not null primary key, 名前 text not null) create table t2 (t2.id integer not null primary key, t1.id integer not null, 国 text not null) よろしくお願いします。

  • pnck
  • お礼率100% (4/4)

質問者が選んだベストアンサー

  • ベストアンサー
  • yamada_g
  • ベストアンサー率68% (258/374)
回答No.1

外部結合すればいいのではないでしょうか。 select t1.id,t1.名前,min(t2.国) as 国 from t1 left join t2 on (t2.t1id = t1.id) group by t1.id,t1.名前; 山田花子さんのアメリカ、イギリスのうち どちらを取得するのかの条件が不明なのでmin関数で取得しています。 そこらへんは適当に修正してください。 一応動作確認はしました。 http://ideone.com/RY2Ku

pnck
質問者

お礼

早速教えていただいたSQLで試してみたところ、 思ったとおりの結果になりました。 結合でちゃんとできたんですね。 精進したいと思います。 本当に助かりました。どうもありがとうございました。

  1. カテゴリ
  2. [技術者向] コンピューター
  3. データベース
  4. その他(データベース)

関連するQ&A

  • データベースの考え方で。

    データベースの考え方、で教えてください。 路線検索と、路線にひもづく駅名の時刻表を表示させる、データベースを作りたいと思っているのですが、 その場合、考え方としては、以下のような感じになるのでしょうか？ 路線名のtbl sql文: create table transit ( id integer not null, name varchar(20) not null ); 路線名tblにひもづく、駅名tbl sql文: create table station ( id integer not null primary key, transit_id integer not null, name varchar(20) not null ); 駅名tblにひもづく、時刻表tbl sql文: create table timetable ( id integer not null primary key, station_id integer not null, hour integer not null, minute integer not null ); 表示パターンとしては、 ================================= ○○線：　XX駅 6:10 20 7:30 55 . . . . . . 22:15 36 ================================== のような感じです。 よろしくお願いします。

  • データベース：二つのテーブルの繋ぎ方

    私（あなた）が検査担当者だとします。 そして、測定担当者から CREATE TABLE measurement ( lot VARCHAR(10) NOT NULL, test_id INTEGER NOT NULL, data NUMERIC(17,7), PRIMARY KEY(lot, test_id) ); という、あるロットでのテスト番号に対する測定値データが送られてきたとします。 そして、設計担当者から CREATE TABLE spec ( test_name VARCHAR(10) NOT NULL, upper_limit NUMERIC(17,7), lower_limit NUMERIC(17,7), PRIMARY KEY(test_name) ); という、テスト項目名に対する上限・下限の仕様データが送られてきたとします。 ここで、テスト番号とテスト項目名は同じ数だけあって、お互いが一致するように同じ順番で並んでいるとします。 この二つのテーブルを使って、測定値が範囲内か範囲外かを調べるのが目的です。 この場合、どうやって二つのテーブルを繋ぐのが一般的ですか？ 自分の考えだと、一旦、specテーブルにtest_idを加えて CREATE TABLE spec ( test_id INTEGER NOT NULL, test_name VARCHAR(10) NOT NULL, upper_limit NUMERIC(17,7), lower_limit NUMERIC(17,7), PRIMARY KEY(test_id, test_name) ); とするのですが、第二（第三？）正規化に基づいて CREATE TABLE spec ( test_id INTEGER NOT NULL, upper_limit NUMERIC(17,7), lower_limit NUMERIC(17,7), PRIMARY KEY(test_id) ); と CREATE TABLE test ( test_id INTEGER NOT NULL, test_name VARCHAR(10), PRIMARY KEY(test_id) ); の二つに分けるのかな、と思っています。 正規化の観点から言うと、きっとこれが正しいのかなと思っています。 でも、元々、specテーブルにはテスト項目名が入っていたのでテスト項目名と上限・下限が一目で分かっていたのに、このように分けると、いちいちspecテーブルでテスト番号を確認してから、testテーブルでそのテスト番号に該当するテスト項目名を見つけなくてはならなくなってしまいます。クエリーでの検索もJOINをしなければならなくなって遅くなりませんか？ 敢えて正規化しないでおく、なんて手段もありますか？ こんな場合、皆さんならどうしますか？ 「こうするように決められている」なのか、 「ああもできるし、こうもできるんですが、どっちでもいいですよ」なのか、 温度差みたいなものもできれば知りたいです。

  • データベース：主キーが文字列の場合IDを振るべき？

    テーブルの主キーが文字列の場合、 その主キーの文字列を通し番号に置き換えて、 その文字列は別テーブルに移したほうが検索が速いですか？ 例えば、テーブルが CREATE TABLE spec ( test_name VARCHAR(40) NOT NULL, upper_limit NUMERIC(17,7), lower_limit NUMERIC(17,7), PRIMARY KEY(test_name) ); で、40文字という長いテスト項目名の場合、 CREATE TABLE spec ( test_id INTEGER NOT NULL, upper_limit NUMERIC(17,7), lower_limit NUMERIC(17,7), PRIMARY KEY(test_id) ); と CREATE TABLE test ( test_id INTEGER NOT NULL, test_name VARCHAR(40) NOT NULL, PRIMARY KEY(test_id) ); という二つのテーブルに分けたほうが検索は速くなりますか？ （もちろん、結合する時間も含めてです。） もし速くなるとしても、文字数が3文字など少ない場合は 通し番号に置き換えてもきっと効果は薄いですよね？ 何文字以上の文字列なら通し番号に置き換えたほうが速いですか？ 皆さんはどのように決めていますか？

  • 結合？の仕方がわからず困っています。

    こんにちは、ほぼ初心者ですがよろしくお願いします。 sqlite2 で以下のような倉庫テーブルと在庫数テーブルがあったとして、 手順５で、ある品物(item_id = 1)の倉庫ごとの在庫数を取得したいと思っています。 倉庫に結合する在庫データがなくても、 倉庫に対する行を取得できる SQL 文を教えていただけませんでしょうか？ ※そもそもテーブルの作り方がおかしいでしょうか？ その辺も含めてアドバイスいただけるとありがたいです。 -- 以下sqlite です .mode column .header ON -- 手順１ 倉庫テーブル CREATE TABLE stock_sites ( id INTEGER NOT NULL PRIMARY KEY , name TEXT NOT NULL ); -- 手順２ 在庫テーブル CREATE TABLE stocks ( id INTEGER NOT NULL PRIMARY KEY , stock_site_id INTEGER NOT NULL, item_id INTEGER NOT NULL, stock INTEGER NOT NULL, UNIQUE(stock_site_id, item_id) ); -- 手順３、倉庫データ INSERT INTO stock_sites(name) VALUES('site_A'); INSERT INTO stock_sites(name) VALUES('site_B'); INSERT INTO stock_sites(name) VALUES('site_C'); -- 手順４、在庫データ INSERT INTO stocks(stock_site_id, item_id, stock) VALUES(1,1,10); INSERT INTO stocks(stock_site_id, item_id, stock) VALUES(2,2,20); -- 手順５ ある品の倉庫ごとの在庫数を取得したい SELECT t1.name AS site_name, t2.* FROM stock_sites AS t1 LEFT JOIN stocks AS t2 ON t1.id = t2.stock_site_id WHERE t2.item_id = 1 ORDER BY t1.id; -- 結果 -- site_name　　t2.id　　　t2.stock_site_id　t2.item_id　t2.stock -- ----------　----------　----------------　----------　---------- -- site_A　　 1　　　　　 1　　　　　　　　 1　　　　　10 -- 欲しい結果 -- site_name　　t2.id　　　t2.stock_site_id　t2.item_id　t2.stock -- ----------　----------　----------------　----------　---------- -- site_A　　 1　　　　　 1　　　　　　　　 1　　　　　10 -- site_B -- site_C

  • create table時のdefaultにLAST_INSERT_ID()を使う

    いつもお世話になってます。 現在、postgreSQL→MySQLへの移行作業をしようとしています。 型が微妙に違う等の修正を手作業で行っているのですが、 postgreSQLの下記の型(default値)をMySQLでどう設定してよいかわからずに困っています。 ■postgreSQLのcreate文 CREATE TABLE test_tab ( id_i serial NOT NULL, parent_id integer DEFAULT currval('inquiry_tab_id_i_seq'::text) ) 下記のような感じでMySQLで出来るかなと思ったらうまく出来ませんでした。 ■試してみたMySQLのcreate文 CREATE TABLE test_tab ( id_i int primary key not null auto_increment, parent_id integer DEFAULT LAST_INSERT_ID() ) どう設定すれば、同じ設定のテーブルが作れるのか、どなたかご教授願えませんでしょうか。 よろしくお願いします。

  • mysqlのインデックスについて質問です。

    mysqlのインデックスについて質問です。 http://archiva.jp/web/server-side/sql_02.html に、 『mysqlでは1つのクエリ実行で、1つのテーブルにつき1つのインデックスしか使用できない』 とあります。 よく以下のようなテーブル定義を見かけますが、 CREATE TABLE IF NOT EXISTS `data` ( `id` int(10) NOT NULL default '0', `user_id` int(10) NOT NULL default '0', `file_id` int(10) NOT NULL default '0', `name` text collate utf8_unicode_ci NOT NULL default '', PRIMARY KEY (`id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; idとuser_idを条件にするクエリであれば、上記制約を満たすため、 KEY `id_user_id_idx` (`id`,`user_id`) の複合クエリを定義すれば良いのでしょうか？ また、idとuser_idとfile_idを条件にするクエリも考慮に入れた場合、 テーブル定義は以下のようにするのでしょうか？ CREATE TABLE IF NOT EXISTS `data` ( `id` int(10) NOT NULL default '0', `user_id` int(10) NOT NULL default '0', `file_id` int(10) NOT NULL default '0', `name` text collate utf8_unicode_ci NOT NULL default '', PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `id_user_id_idx` (`id`,`user_id`), KEY `id_user_id_file_id_idx` (`id`,`user_id`,`file_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 最後に、create table 時のインデックスの指定は、 INDEX `user_id` (`user_id`), のようにもできますが、INDEXとKEYの違いは何かあるのでしょうか。

    • ベストアンサー
    • MySQL
  • 指定した条件の組み合わせのSQL文

    SQLite　バージョン3.7.10　を使用しています。 以下のようなテーブル構造で、 「t1」テーブルの全ての行と 「t6」テーブルのdateカラムの年の部分を グループ化して全ての組み合わせの 「t6」テーブルのamount の合計を出力したいのですが、 どのようなSQL文を書けば良いのでしょうか？ 例えばこのデータ内容なら、以下のような結果が得たいです。 +------+-------+-------+ |　year　|　t1_name　|　合計　| +------+-------+-------+ |　2011　|t1_name1|　　5000| +------+-------+------+ |　2012　|t1_name1|　10000| +------+-------+------+ |　2012　|t1_name2|　45000| +------+-------+------+ PRAGMA foreign_keys = ON; DROP TABLE IF EXISTS t6; DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( key TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL UNIQUE ); INSERT INTO t1 (key, name) VALUES ('t1_key1', 't1_name1'); INSERT INTO t1 (key, name) VALUES ('t1_key2', 't1_name2'); CREATE TABLE t6 ( no INTEGER PRIMARY KEY AUTOINCREMENT, t1_key TEXT NOT NULL, date TEXT NOT NULL, amount INTEGER NOT NULL, FOREIGN KEY(t1_key) REFERENCES t1(key) ON DELETE CASCADE ); CREATE INDEX date on t6(date); INSERT INTO t6 (t1_key, date, amount) VALUES ('t1_key1', '2011-01-01', 5000); INSERT INTO t6 (t1_key, date, amount) VALUES ('t1_key1', '2012-01-01', 10000); INSERT INTO t6 (t1_key, date, amount) VALUES ('t1_key2', '2012-01-01', 15000); INSERT INTO t6 (t1_key, date, amount) VALUES ('t1_key2', '2012-02-01', 30000); http://ideone.com/I1mMba

  • SQLのPRIMARY KEYの後のKEYについて

    SQLの超初心者です。 SQLのPRIMARY KEYの後のKEYについてご教授ください。m(__)m CREATE TABLE ***** ( 　　shipping_id NOT NULL default 0, 　　order_date datetime NOT NULL default 0000-00-00 00:00:00, 　　　　・ 　　　　・ 　　PRIMARY KEY (shipping_id), 　　KEY order_date (order_date) ) の様に参考書に記載があるのですが、 KEY order_date (order_date) の部分の意味がわかりません。 どなたか詳しい方アドバイスお願いします。

  • PostgreSQLからMySQLへ変換

    現在ECサイトに追加カスタマイズ機能を導入したいと考えているのですが、 その際の使用コードが以下のようにPostgreSQLの記載となっており、 使用しているphpMyAdminにてSQLコードを入力するとエラーとなってしまいます。 ↓PostgreSQLコード CREATE TABLE dtb_bookmark ( bookmark_id serial NOT NULL, customer_id integer NOT NULL, product_id integer NOT NULL, memo text, create_date timestamp without time zone NOT NULL, update_date timestamp without time zone NOT NULL, del_flg integer ); ALTER TABLE ONLY dtb_bookmark ADD CONSTRAINT dtb_bookmark_pkey PRIMARY KEY (bookmark_id); COMMENT ON TABLE dtb_bookmark IS 'お気に入り情報'; エラーはtimestamp型とALTER以降の文で発生致します。 mysql文にて解釈させるにはどのように変更したらよろしいのでしょうか？ 使用mysqlバージョンはMySQL 4.1.21です。 アドバイスなど頂けましたら幸いです。 よろしくお願い致します。

    • ベストアンサー
    • MySQL
  • SQL文について

    SQLite　バージョン3.7.10　を使用しています。 次のようなテーブル構造で、 PRAGMA foreign_keys = ON; CREATE TABLE t1 ( key TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL UNIQUE ); INSERT INTO t1 (key, name) VALUES ('t1_key1', 't1_name1'); INSERT INTO t1 (key, name) VALUES ('t1_key2', 't1_name2'); CREATE TABLE t2 ( key TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL UNIQUE ); INSERT INTO t2 (key, name) VALUES ('t2_key1', 't2_name1'); INSERT INTO t2 (key, name) VALUES ('t2_key2', 't2_name2'); CREATE TABLE t3 ( key TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL UNIQUE ); INSERT INTO t3 (key, name) VALUES ('t3_key1', 't3_name1'); INSERT INTO t3 (key, name) VALUES ('t3_key2', 't3_name2'); CREATE TABLE t4 ( year TEXT NOT NULL, t1_key TEXT NOT NULL, t3_key TEXT NOT NULL, percent INTEGER NOT NULL, UNIQUE(year, t1_key, t3_key), FOREIGN KEY(t1_key) REFERENCES t1(key) ON DELETE CASCADE, FOREIGN KEY(t3_key) REFERENCES t3(key) ON DELETE CASCADE ); INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2011', 't1_key1', 't3_key1', 10); INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2012', 't1_key2', 't3_key2', 50); INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2012', 't1_key2', 't3_key1', 100); CREATE TABLE t5 ( no INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT NOT NULL, t1_key TEXT NOT NULL, t2_key TEXT NOT NULL, t3_key TEXT NOT NULL, amount INTEGER NOT NULL, FOREIGN KEY(t1_key) REFERENCES t1(key) ON DELETE CASCADE, FOREIGN KEY(t2_key) REFERENCES t2(key) ON DELETE CASCADE, FOREIGN KEY(t3_key) REFERENCES t3(key) ON DELETE CASCADE ); INSERT INTO t5 (date, t1_key, t2_key, t3_key, amount) VALUES ('2011-01-01', 't1_key1', 't2_key1', 't3_key1', 3000); INSERT INTO t5 (date, t1_key, t2_key, t3_key, amount) VALUES ('2011-02-02', 't1_key2', 't2_key1', 't3_key1', 2000); INSERT INTO t5 (date, t1_key, t2_key, t3_key, amount) VALUES ('2012-01-01', 't1_key2', 't2_key1', 't3_key1', 5000); 例えばテーブル「t5」から、 dateカラム＝’2011’ t1_keyカラム＝ (テーブル「t1」のt1_nameカラム＝’t1_name1’のt1_key1) t2_keyカラム＝ (テーブル「t2」のt2_nameカラム＝’t2_name1’のt2_key1) という条件の含まれてる全ての対応する行に、 テーブル「t4」のpercentカラムとテーブル「t5」のamountカラムを 掛け合わせた数値の合計を出力する。 ただしテーブル「t4」に対応するt1_keyとt3_keyがなければ、 percentを「0」としてamountと掛ける。 例えばこの条件だと '2011-01-01'と'2011-02-02'の2つの行のamountの合計を求めることになりますが、 '2011-01-01'の方は、テーブル「t4」にpercent「10」があるので　3000×(10/100)=300 '2011-02-02'の方は、テーブル「t4」にpercentがない（対応する行がない）ので　2000×(0/100)=0 合計　300＋0=300 という結果が得たいです。 これはどのようなSQL文になるのでしょうか？

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する