MySQL5.1のSQL文に関する質問です。

以下の2つのテーブルをjointし(3)の形で配列を作りたいのですが、SQL文はどのように書けばいいでしょうか?
・i_idでジョイント
・item_tblにあるアイテムはすべて出力
・zaiko_tblのu_idが2のレコードのみを抽出
・上記2つの条件をかなえflagで抜けているところはnull
・配列のu_idで抜けているところは、2が入ってくれると嬉しいがsqlでできない場合nullでもok
よろしくお願いします。

(1)テーブル名:item_tbl(Pkey i_id)
i_id, i_name
1, aaa
2, bbb
3, ccc
4, ddd
5, eee

(2)テーブル名:zaiko_tbl(Pkey u_id, i_id)
u_id, i_id, flag
2, 1, 0
6, 3, 1
8, 2, 1
2, 2, 1
9, 4, 0
8, 4, 1
2, 4, 1

出力したい配列
i_id, u_id, i_name, flag
1, 2, aaa, 0
2, 2, bbb, 1
3, 2, ccc, null
4, 2, ddd, 1
5, 2, eee, null

回答の条件
  • 1人2回まで
  • 13歳以上
  • 登録:2011/04/02 16:56:42
  • 終了:2011/04/02 20:52:02

ベストアンサー

id:windofjuly No.2

うぃんど回答回数2625ベストアンサー獲得回数11492011/04/02 19:43:52

ポイント100pt

SQLの処理手順はFROM、LEFT JOIN、WHERE、SELECTの順に実施されますので、下記(1)のように先に絞り込んでおくか、(2)のように最後に付けるかといった対応になります

(1)

SELECT a.i_id
    , 2 AS u_id
    , a.i_name
    , b.flag
FROM item_tbl a
LEFT JOIN (SELECT i_id, flag FROM zaiko_tbl WHERE u_id = 2) b ON b.i_id = a.i_id
;

zaiko_tblテーブルに該当するレコードが複数あってもちゃんと出力できる

(2)

SELECT a.i_id
    , 2 AS u_id
    , a.i_name
    , (SELECT b.flag FROM zaiko_tbl b WHERE b.u_id = 2 AND b.i_id = a.i_id LIMIT 1) AS flag
FROM item_tbl a
;

zaiko_tblテーブルに該当するレコードが複数あるとエラーになってしまうためLIMIT 1として制限をかけている

最新のデータのみでよい場合などは下記のようにして絞り込む

    , (SELECT b.flag FROM zaiko_tbl b WHERE b.u_id = 2 AND b.i_id = a.i_id ORDER BY 日付 DESC LIMIT 1) AS flag

 

どちらを採用するかは必要性(複数レコードの場合の取り扱い方)でまずは選びます

どちらでも良い場合はEXPLAINで実行計画を読み取ります(これは難しい作業となりますので、初心者のうちは飛ばしても良いです)

プロでも最終的に行うことですが、実運用と同じような量と質のテストデータを用意して実際に作動させて速度の違いを調べたりもします

id:seadwell

前回に引き続き丁重なご回答ありがとうございます。

動作を確認しました。


> FROM、LEFT JOIN、WHERE、SELECTの順に・・・

こんなの初めて知りました。

また、SQL文もはじめてみる文法です。前回いろいろな組み合わせといっていた意味がほんの少し判った気がしました。

> EXPLAINで実行計画を読み取ります

これもはじめて聞きました。私なりに早速調べてみます。

初めてだらけでとても勉強になった回答でした。

ありがとうございます。

2011/04/02 20:51:42

その他の回答(1件)

id:asuka645 No.1

あすか回答回数856ベストアンサー獲得回数972011/04/02 17:27:43

ポイント28pt

こんな感じでどうでしょう。

SELECT item_tbl.i_id AS i_id, zaiko_tbl.u_id AS u_id, item_tbl.i_name AS i_name, zaiko_tbl.flag AS flag FROM item_tbl LEFT JOIN zaiko_tbl ON item_tbl.i_id = zaiko_tbl.i_id WHERE u_id = 2;
id:seadwell

ご回答ありがとうございます。

これは試したんですが、zaiko_tblに一件もデータが入っていないと空が出力されるんですよ。

なんででしょうね?

私もこれで取れると思ったんですが・・・。。

『item_tblにあるアイテムはすべて出力』の条件は生きてほしいのです。

つまり、2で抽出するデータがない場合item_tblの一覧にnullだらけの配列がほしいのです。

ちなみに、WHEREを外すとすべて出力されますので記述にバグはないと思います。

2011/04/02 18:57:19
id:windofjuly No.2

うぃんど回答回数2625ベストアンサー獲得回数11492011/04/02 19:43:52ここでベストアンサー

ポイント100pt

SQLの処理手順はFROM、LEFT JOIN、WHERE、SELECTの順に実施されますので、下記(1)のように先に絞り込んでおくか、(2)のように最後に付けるかといった対応になります

(1)

SELECT a.i_id
    , 2 AS u_id
    , a.i_name
    , b.flag
FROM item_tbl a
LEFT JOIN (SELECT i_id, flag FROM zaiko_tbl WHERE u_id = 2) b ON b.i_id = a.i_id
;

zaiko_tblテーブルに該当するレコードが複数あってもちゃんと出力できる

(2)

SELECT a.i_id
    , 2 AS u_id
    , a.i_name
    , (SELECT b.flag FROM zaiko_tbl b WHERE b.u_id = 2 AND b.i_id = a.i_id LIMIT 1) AS flag
FROM item_tbl a
;

zaiko_tblテーブルに該当するレコードが複数あるとエラーになってしまうためLIMIT 1として制限をかけている

最新のデータのみでよい場合などは下記のようにして絞り込む

    , (SELECT b.flag FROM zaiko_tbl b WHERE b.u_id = 2 AND b.i_id = a.i_id ORDER BY 日付 DESC LIMIT 1) AS flag

 

どちらを採用するかは必要性(複数レコードの場合の取り扱い方)でまずは選びます

どちらでも良い場合はEXPLAINで実行計画を読み取ります(これは難しい作業となりますので、初心者のうちは飛ばしても良いです)

プロでも最終的に行うことですが、実運用と同じような量と質のテストデータを用意して実際に作動させて速度の違いを調べたりもします

id:seadwell

前回に引き続き丁重なご回答ありがとうございます。

動作を確認しました。


> FROM、LEFT JOIN、WHERE、SELECTの順に・・・

こんなの初めて知りました。

また、SQL文もはじめてみる文法です。前回いろいろな組み合わせといっていた意味がほんの少し判った気がしました。

> EXPLAINで実行計画を読み取ります

これもはじめて聞きました。私なりに早速調べてみます。

初めてだらけでとても勉強になった回答でした。

ありがとうございます。

2011/04/02 20:51:42
  • id:windofjuly
    うぃんど 2011/04/02 21:55:53
    >はじめてみる文法
     
    サブクエリあるいは副問い合わせと呼ばれる構文です
    http://dev.mysql.com/doc/refman/5.1/ja/subqueries.html
     
    >EXPLAIN

    EXPLAINはチューニングするというレベルの話になります
    http://dev.mysql.com/doc/refman/5.1/ja/query-speed.html
     
    >前回いろいろな組み合わせ
    私は、将来のため一言二言多め(今回であればEXPLAIN)に情報を入れて置くようにする場合が多いのですが、前回の「色々な組み合わせ」につきましては前回コメントしたUPDATE文のWHERE句だけを指しています
    http://q.hatena.ne.jp/1301583132#c198397

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません