質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.99%

mysql where句 複数テーブルで複数条件の摘出

解決済

回答 4

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,563

shimpei

score 11

お世話になります。 
mysqlを二日前から勉強し始めた超初心者です。 
まったくの初心者なのですが、自力での解決が難しそうなので、教えて下さい。 

<現状の問題点>

後述のSQLのWhere句の条件に沿って、テーブル①から2レコード目だけを抽出したいのですが、
想定通りの結果になりません。
下記の「実現したい実行結果」を表示させるやり方を教えて下さい。
よろしくお願い致します。
また、可能であれば、現在のSQLの何が悪いか教えて頂けると幸いです。

※表示させたいレコードの条件について
下記2つの条件を満たすレコードを抽出したいです。
・条件①:
「mgmt_server_backup」テーブルから、下記の有効期限切れのデータを抽出する。
(job_status=0、lock_flg=0、del_flg=0、expire_date is not null、expire_date<now)

・条件②:
上記で抽出したレコードの中で、共有されていないレコードを抽出する。
「mgmt_server_backup」テーブルの「mgmt_server_backup_id」と、
「mgmt_server_backup_share」テーブルの「mgmt_server_backup_id」が一致しない。

元々のSQLは条件①のみの以下だったので、できれば、少ない修正で実現できればと考えております。
■元々のSQL(現行)

select
    mgmt_server_backup_id as mgmtServerBackupId,
    mst_customer_code as mstCustomerCode
from
    mgmt_server_backup
where
    job_status = 0 and
    lock_flg = 0 and
    del_flg = 0 and
    expire_date is not null and
    expire_date >= NOW();

■現在の実行結果

mgmtServerBackupId   mstCustomerCode
11 10000001
22 3
22 3

■実現したい実行結果

mgmtServerBackupId   mstCustomerCode
22 3

<実行環境情報>
以下の2つのテーブルがあります。 
■テーブル①:mgmt_server_backup

mgmt_server_backup_id mst_customer_code job_status lock_flg   del_flg   expire_date
11 10000001 0 0 0 2020/05/24
22 3 0 0 0 2020/05/24

■テーブル②:mgmt_server_backup_share

mgmt_server_backup_id del_flg
11 0
33 0

■実行SQL

select
    msb.mgmt_server_backup_id as mgmtServerBackupId,
    msb.mst_customer_code as mstCustomerCode
from
    mgmt_server_backup AS msb, mgmt_server_backup_share AS msbs
where
    msb.mgmt_server_backup_id <> msbs.mgmt_server_backup_id and
    msbs.del_flg = 0 and
    msb.job_status = 0 and
    msb.lock_flg = 0 and
    msb.del_flg = 0 and
    msb.expire_date is not null and
    msb.expire_date >= NOW()
;
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • hatena19

    2017/05/24 17:30

    表は表タグ、コードはコードタグで囲んでください。上のツールバーで入力できます。Markdown記法です。

    キャンセル

  • KiyoshiMotoki

    2017/05/24 17:45

    「表示させたいレコードの条件」を、文章でも記述してください。現状の SQL文で意図する結果が得られないということは、「SQLのWhere句の条件」自体に誤りがある可能性もあるからです。

    キャンセル

  • KiyoshiMotoki

    2017/05/24 18:42

    shimpei様。情報の追記、ありがとうございます。回答を記載しましたので、ご確認ください。

    キャンセル

回答 4

checkベストアンサー

+3

select
    msb.mgmt_server_backup_id as mgmtServerBackupId, 
    msb.mst_customer_code as mstCustomerCode 
from
    mgmt_server_backup AS msb
left outer join
    mgmt_server_backup_share AS msbs ON msb.mgmt_server_backup_id = msbs.mgmt_server_backup_id
where
    msb.job_status = 0 and 
    msb.lock_flg = 0 and 
    msb.del_flg = 0 and 
    msb.expire_date is not null and 
    msb.expire_date < NOW() and
    msbs.mgmt_server_backup_id is null;


実行結果
http://sqlfiddle.com/#!9/140fbb/1


可能であれば、現在のSQLの何が悪いか教えて頂けると幸いです。

については、mgmt_server_backupテーブルとmgmt_server_backup_shareの結合条件に問題があります。

質問欄にご提示の

from
    mgmt_server_backup AS msb, mgmt_server_backup_share AS msbs
where
    msb.mgmt_server_backup_id <> msbs.mgmt_server_backup_id and


という結合方法は
mgmt_server_backup_idが互いに異なるレコード同士を結合する」
という意味になります。

これは互いに無関係なレコード同士を結合しているだけですので、ここから
mgmt_server_backupテーブルに存在し、
mgmt_server_backup_shareテーブルには存在しないmgmt_server_backup_id
をうまく抽出することはできません。

意図する結果を得るためには、私の回答の

from
    mgmt_server_backup AS msb
left outer join
    mgmt_server_backup_share AS msbs ON msb.mgmt_server_backup_id = msbs.mgmt_server_backup_id


部分で
「同じmgmt_server_backup_idを持つレコードを左外部結合」
し、その上で

msbs.mgmt_server_backup_id is null


という WHERE条件により、
「結合条件を満たすmgmt_server_backup_shareテーブルのレコードが存在しなかった
mgmt_server_backupテーブルのレコード」
を絞り込む必要がありました。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/05/24 18:50

    サブクエリを使わなても、外部契合してwhere条件だけでできるのですね。私のよりこっちの方がスッキリしていいですね。

    キャンセル

  • 2017/05/24 20:04

    丁寧な回答ありがとうございます。
    「msbs.mgmt_server_backup_id is null」について、理解するのに時間がかかりましたが、内容を理解することができました。
    本当にありがとうございました!

    キャンセル

  • 2017/05/24 20:07

    よくみたら、質問の実行SQLにある
    msbs.del_flg = 0
    が抜けてますね。もし、mgmt_server_backup_share に、
    (22, 1)
    というレコードがあった場合、抽出されないですね。
    http://sqlfiddle.com/#!9/4d329/8
    かな?

    キャンセル

  • 2017/05/24 21:26

    shimpei様

    解決できたようで、何よりです。
    > 現在のSQLの何が悪いか
    について若干、追記しましたので、興味があれば確認してみてください。

    hatena19様

    コメント及び補足、ありがとうございます。
    msbs.del_flg = 0 が「本来、意図する抽出条件」にどのように関わってくるか不明だったため、
    除外させていただきました。

    当カラムが論理削除を意味するものであれば、ご提示いただいた SQL文が正解となりますね。

    キャンセル

0

LIMIT 1,1をつけて検索してみてはいかがですか?
ちなみにSQLはORDER BY で明示しない限り
表示順は保証されていません。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/05/24 18:32

    ご回答頂きありがとうございます。
    お手数ですが、LIMIT 1,1について、どのように修正すればよろしいでしょうか?

    キャンセル

  • 2017/05/24 18:43

    LIMIT1,1を試した結果、期待した結果になったのですが、これはあくまで、質問するためのテストデータでの話なので、実際に何百件もある場合を考えると、正式には難しいと考えています。
    お手数をお掛けしますが、何か他にいいやり方はご存じないでしょうか?

    キャンセル

0

条件がいまいち不明瞭ですが、下記のようなことかな?

select 
msb.mgmt_server_backup_id as mgmtServerBackupId, 
msb.mst_customer_code as mstCustomerCode 
from
 (select * from mgmt_server_backup
  where
   job_status = 0 and 
   lock_flg = 0 and 
   del_flg = 0 and 
   expire_date >= NOW() 
 ) as  msb
 left join
 (select * from mgmt_server_backup_share
  where
   del_flg = 0
 ) as msbs
on
 msb.mgmt_server_backup_id = msbs.mgmt_server_backup_id
where
 msbs.mgmt_server_backup_id is null;

言葉にすると、各テーブルを抽出したもの同士を比較して、テーブル②に存在しないテーブル①のデータを抽出。

追記

元々のSQLは条件①のみの以下だったので、できれば、少ない修正で実現できればと考えております。 

select
    mgmt_server_backup_id as mgmtServerBackupId,
    mst_customer_code as mstCustomerCode
from
    mgmt_server_backup
where
    job_status = 0 and
    lock_flg = 0 and
    del_flg = 0 and
    expire_date is not null and
    expire_date >= NOW() and
    not exsist
    (select * from mgmt_server_backup_share
     where
      del_flg = 0 and
      mgmt_server_backup_share.mgmt_server_backup_id = mgmt_server_backup_share.mgmt_server_backup_id
    );


元々のSQLに not exsist 以下を追加しただけです。ただし、下記にもあるように、上のSQLより遅くなる場合が多いです。

MySQLで集合差を出す - Qiita

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/05/24 18:31

    ご回答頂きありがとうございます。
    条件について、下記の記載を追記致しました。
    「※表示させたいレコードの条件について ~」

    できれば、「元々のSQL(現行)」をあまり修正しない形を望んでおります。大変お手数ですが、よろしくお願いします。

    キャンセル

0

情報があまりに少ないので、憶測を交えてとりあえず再現してみました。

CREATE TABLE `mgmt_server_backup` (
  `mgmt_server_backup_id` int(11) NOT NULL,
  `mst_customer_code` varchar(8) NOT NULL,
  `job_status` int(11) NOT NULL,
  `lock_flg` tinyint(1) NOT NULL,
  `del_flg` tinyint(1) NOT NULL,
  `expire_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `mgmt_server_backup` (`mgmt_server_backup_id`, `mst_customer_code`, `job_status`, `lock_flg`, `del_flg`, `expire_date`) VALUES
(11, '10000001', 0, 0, 0, '2020-05-24'),
(22, '3', 0, 0, 0, '2020-05-24');

CREATE TABLE `mgmt_server_backup_share` (
  `mgmt_server_backup_id` int(11) NOT NULL,
  `del_flg` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `mgmt_server_backup_share` (`mgmt_server_backup_id`, `del_flg`) VALUES
(11, 0),
(33, 0);

ここまでで違うところがあれば教えてください。

次に、問題のSQLを実行してみます。

SELECT
  msb.mgmt_server_backup_id AS mgmtServerBackupId,
  msb.mst_customer_code AS mstCustomerCode
FROM
  mgmt_server_backup AS msb,
  mgmt_server_backup_share AS msbs
WHERE
  msb.mgmt_server_backup_id <> msbs.mgmt_server_backup_id
  AND msbs.del_flg = 0
  AND msb.job_status = 0
  AND msb.lock_flg = 0
  AND msb.del_flg = 0
  AND msb.expire_date IS NOT NULL
  AND msb.expire_date >= NOW();
mgmtServerBackupId mstCustomerCode
22 3
11 10000001
22 3

ここまで再現できました。

さて、検証するにあたり、SELECTを一旦全選択にしてみます。
また、条件も外してしまいましょう。

SELECT
  *
FROM
  mgmt_server_backup AS msb,
  mgmt_server_backup_share AS msbs
WHERE
  1 = 1;
mgmt_server_backup_id mst_customer_code job_status lock_flg del_flg expire_date mgmt_server_backup_id del_flg
11 10000001 0 0 0 2020-05-24 11 0
22 3 0 0 0 2020-05-24 11 0
11 10000001 0 0 0 2020-05-24 33 0
22 3 0 0 0 2020-05-24 33 0

この結果を見る限り、以下の WHERE句のみで絞り込みが実施されていることがわかります。

msb.mgmt_server_backup_id <> msbs.mgmt_server_backup_id


他の部分は、必要ないわけではありませんが、今回のレコードでは関係ありません。
すべてのレコードが該当するからです。

  AND msbs.del_flg = 0
  AND msb.job_status = 0
  AND msb.lock_flg = 0
  AND msb.del_flg = 0
  AND msb.expire_date IS NOT NULL
  AND msb.expire_date >= NOW();

WHERE句の働きを見たうえで再度、WHEREをかけて実行してみます。

SELECT
  *
FROM
  mgmt_server_backup AS msb,
  mgmt_server_backup_share AS msbs
WHERE
  msb.mgmt_server_backup_id <> msbs.mgmt_server_backup_id
  AND msbs.del_flg = 0
  AND msb.job_status = 0
  AND msb.lock_flg = 0
  AND msb.del_flg = 0
  AND msb.expire_date IS NOT NULL
  AND msb.expire_date >= NOW();
mgmt_server_backup_id mst_customer_code job_status lock_flg del_flg expire_date mgmt_server_backup_id del_flg
22 3 0 0 0 2020-05-24 11 0
11 10000001 0 0 0 2020-05-24 33 0
22 3 0 0 0 2020-05-24 33 0

ここからは、shimpei さんがどのような値を取りたいのかによるのでなんとも言えませんが、ここも憶測で以下のように定義してみます。

mgmtServerBackupId と mstCustomerCode の組み合わせが一致するレコードが複数ある場合

mgmtServerBackupId と mstCustomerCode の組み合わせが一致する ということは、mgmtServerBackupId と mstCustomerCode の組み合わせでグループ化することで、実現することが出来ます。
グループ化して、該当のレコードがいくつあるかをカウントする処理まで書いてみましょう。

SELECT
  msb.mgmt_server_backup_id AS mgmtServerBackupId,
  msb.mst_customer_code AS mstCustomerCode,
  COUNT(*)
FROM
  mgmt_server_backup AS msb,
  mgmt_server_backup_share AS msbs
WHERE
  msb.mgmt_server_backup_id <> msbs.mgmt_server_backup_id
  AND msbs.del_flg = 0
  AND msb.job_status = 0
  AND msb.lock_flg = 0
  AND msb.del_flg = 0
  AND msb.expire_date IS NOT NULL
  AND msb.expire_date >= NOW()
GROUP BY
  msb.mgmt_server_backup_id, msb.mst_customer_code;
mgmtServerBackupId mstCustomerCode COUNT(*)
11 10000001 1
22 3 2

一つポイントとして、GROUP BY は WHERE の後に実行されます。
ですので、その結果を WHERE として指定することは出来ません。
この場合は HAVING 句を使います。

SELECT
  msb.mgmt_server_backup_id AS mgmtServerBackupId,
  msb.mst_customer_code AS mstCustomerCode
FROM
  mgmt_server_backup AS msb,
  mgmt_server_backup_share AS msbs
WHERE
  msb.mgmt_server_backup_id <> msbs.mgmt_server_backup_id
  AND msbs.del_flg = 0
  AND msb.job_status = 0
  AND msb.lock_flg = 0
  AND msb.del_flg = 0
  AND msb.expire_date IS NOT NULL
  AND msb.expire_date >= NOW()
GROUP BY
  msb.mgmt_server_backup_id, msb.mst_customer_code
HAVING
  COUNT(*) > 1;
mgmtServerBackupId mstCustomerCode
22 3

無事に、最初の条件を満たす式がかけました。

ちなみに、”msb.expire_date >= NOW()” を指定している時点でNULLは除かれるので ”msb.expire_date IS NOT NULL” は必要ありません。

また、今回のSQL文は説明重視で書いているため、とても実用的ではありません。
あくまで参考としてください。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/05/24 19:20

    丁寧な回答ありがとうございます。
    >ここからは、shimpei さんがどのような値を取りたいのかによるのでなんとも言えませんが
    今回の変更は、上記質問に追記した、条件②になるので、2つのテーブルの「mgmt_server_backup_id」が一致してないレコードを抽出したいのです。
    なので、Group byでまとめないで、「mgmtServerBackupId」が「22」のレコード1件が抽出できると考えています。この方法を教えて頂けないでしょうか?質問が下手で申し訳ありません。
    ・条件②:
    上記で抽出したレコードの中で、共有されていないレコードを抽出する。
    「mgmt_server_backup」テーブルの「mgmt_server_backup_id」と、
    「mgmt_server_backup_share」テーブルの「mgmt_server_backup_id」が一致しない。

    キャンセル

  • 2017/05/24 22:15

    ごめんなさい、回答中に追記がされていたのを見ていませんでした^^;
    解決したようで何よりです!

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.99%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる