MySQLで、顧客の予約情報をある条件で抽出したいのですが不明です。

Question

MySQLで、JAVAを使ってＳＱＬ文を作っているのですが、行き詰っています。

顧客マスタと、顧客の予約テーブルがあり、
顧客の予約テーブルには、同じ顧客の予約レコードが多数入っています。

そのマスタとテーブルの両方の情報を使いたいのでJOINし、
下記、抽出条件で、ＳＱＬ文を書けないでしょうか？

■抽出条件
１．今日以降に予約があれば、全て抽出。
⇒尚、一人の顧客の予約が複数表示されてもよい＝未来の全ての予約を表示。
２．今日以降に予約がなければ、昨日含めて過去の予約の内、一番最近のレコードを抽出。
⇒尚、一人の顧客の予約は、１件だけ表示。
３．予約日の大きい順で、抽出。

■テーブル概要
costomer 顧客テーブル
⇒ カラム情報： 顧客ID,顧客名,顧客住所,顧客TEL,その他・・・,論理削除フラグ：delete_flag
⇒ 顧客のレコードは、compay_idでユニーク。 2015/8/13追記

costomer_reservation 顧客予約テーブル
⇒ カラム情報ヘディングのテキスト ： 顧客ID,予約日,予約時間,予約メモ,その他・・・,論理削除フラグ：delete_flag
⇒ 顧客の予約レコードが複数存在あり（過去、未来共）2015/8/13追記

２つのテーブルの紐づけは、顧客ID：costomer_id。

■書きかけのＳＱＬ文（上記のように抽出されない）
String strSQL =
// １．今日以降に予約があれば、全て抽出。
// ⇒尚、一人の顧客の予約が複数表示されてもよい＝未来の全ての予約を表示。
"SELECT costomer., costomer_reservation."
+ " FROM costomer LEFT OUTER JOIN costomer_reservation ON costomer.costomer_id = costomer_reservation.costomer_id"
+ " WHERE"
+ " (costomer.compay_id = " + iParaCompay_id + ")" // 会社ID
+ " AND (costomer_reservation.reservation_date >= CURRENT_DATE)" // 今日以降の予約日を抽出
+ " AND (costomer_reservation.delete_flag = false)"
+ " AND (costomer.delete_flag = false)"

    // ２．今日以降に予約がなければ、昨日含めて過去の予約の内、一番最近のレコードを抽出。
    //     ⇒尚、一人の顧客の予約は、１件だけ表示。

NG? + " AND NOT EXISTS ("
NG? + " SELECT * FROM costomer_reservation as tmp"
NG? + " WHERE costomer_reservation.costomer_id = tmp.costomer_id"
NG? + " AND (tmp.reservation_date <= CURRENT)" // 過去の予約日を抽出
NG? + " AND (costomer_reservation.reservation_date < tmp.reservation_date)"
NG? + " )"

    // 予約日の大きい順で抽出
    + " ORDER BY costomer_reservation.reservation_date DESC";

Answer 1

1と2で条件がかなり異なるので、多少コードが長くなってもSQLを2つ作ってUNIONで繋げたほうが、メンテナンスがやりやすくなるかも知れませんね。

###2015/8/12追記

同じ名前の項目があると、取り出せないですよね？

縦につなげるだけなので、要素数や要素名は普通にSELECTした場合と同じだと思うのですが、
reservation_dateが2つになるというのはどんな時でしょう？

抽出条件に従うと
条件1(と3)を抽出するSQLはこんな感じ

SELECT cos.*, res.*
FROM costomer cos 
INNER JOIN costomer_reservation res ON cos.costomer_id = res.costomer_id
WHERE res.reservation_date >= CURRENT_DATE
ORDER BY res.reservation_date DESC

条件2(と3)を抽出するSQLはこんな感じ

SELECT cos.*, res.*
FROM costomer cos 
INNER JOIN (
  SELECT *
  FROM costomer_reservation r1
  WHERE NOT EXISTS (
    SELECT * 
    FROM costomer_reservation r2 
    WHERE r1.costomer_id = r2.costomer_id
    AND r1.reservation_date < CURRENT_DATE
    AND r1.res.reservation_date < r2.res.reservation_date
  )
) res ON cos.costomer_id = res.costomer_id
ORDER BY res.reservation_date DESC

UNIONで繋ぐとこんな感じ

(SELECT cos.*, res.*
  FROM costomer cos 
  INNER JOIN costomer_reservation res ON cos.costomer_id = res.costomer_id
  WHERE res.reservation_date >= CURRENT_DATE
)

UNION

(SELECT cos.*, res.*
  FROM costomer cos 
  INNER JOIN (
    SELECT *
    FROM costomer_reservation r1
    WHERE NOT EXISTS (
      SELECT * 
      FROM costomer_reservation r2 
      WHERE r1.costomer_id = r2.costomer_id
      AND r1.reservation_date < CURRENT_DATE
      AND r1.reservation_date < r2.reservation_date
    )
  ) res ON cos.costomer_id = res.costomer_id
)

ORDER BY res.reservation_date DESC

要素が同じなら抵抗無く連結できると思います。

※動作テストはしていないのでエラーがあるかも知れません。
※フラグやクリニックチェックは条件に無かったので省いています
※条件2の方は、costomer_idとreservation_dateの2つがまったく同じレコードは無い前提です。
※条件2の方は相関サブクエリを使っており、レコードが多い場合は別の方法にしないと異様に遅いかも知れません。

Comments

[退会済みユーザー]

ご回答ありがとうございます。
そうですね、おっしゃるとおりです。
当初、UNIONでくっつけようとしていて、その案もあります。

UNIONを使うと、抽出結果の値を取り出すときに、「costomer_reservation.reservation_date 」とテーブル指定ができなくて、「reservation_date 」という項目で取り出さないといけないので、同じ名前の項目があると、取り出せないですよね？

上記、本来のところは、わかりませんが、
実際にやったら、そうだったので、たぶん、そうじゃないかと思っています。
＝同じ項目名の項目が、取り出せなくなる？？ コンパイルエラーになる？？

UNIONで、ＳＱＬ文を作るのを頑張ってみます。

[hirohiro]

> 同じ名前の項目があると、取り出せないですよね？
サンプルコードを追記してみますね。
追記したものは、速度が遅かったり、テーブル側の制約が不十分だと「一人の顧客の予約は、１件だけ」を満たさなかったりするかも知れません。

[退会済みユーザー]

サンプルソースありがとうございます。
どうようしか、ずっと昨日、悩んでいました。
非常に助かります。
いただいたＳＱＬを埋め込んで実施していみます。
大変お手数をおかけし申し訳ありません。
本当に、心からありがとうございます。

[退会済みユーザー]

ご報告：
「Table 'res' from one of the SELECTs cannot be used in global ORDER clause」のエラーがでて、どうやら、SELECTが()で囲ってあるから、
「ORDER BY res.reservation_date DESC」の
resのFROMがない為のエラーのような気がします。超初心者の感ですが．．．
ちょっと、ＳＱＬを何とかしてみます。
それ以外のエラーは今のところでていないので、
これを乗り越えれば、なんとか動きそうな気がします。

[eripong]

ORDER BY res.reservation_date DESC
を
ORDER BY reservation_date DESC
にしたらどうでしょうか？

[退会済みユーザー]

すごいです！！ 一発で、そのエラーがなくなりました。その発想はありませんでした。
ありがとうございます。そのエラーは、回避できたようです。

でも、次のエラーがでました。
「SQLException:Base table or view not found, message from server: \"Unknown table 'cos'」
これは、テーブルがないと言っていると読み取っていますが、
つづりを何度確認しても、他の動いているＳＱＬのテーブル名をコピぺしても、
やはり、同じエラーなので、テーブル名が間違っているのではないです。

[退会済みユーザー]

あっ、おもいついたのがあるので、やってみます。

[退会済みユーザー]

" (SELECT cos.*, res.*" を勘違いして、勝手に置き換えて、
(SELECT costomer.*, costomer_reservation.*にしていたのが、原因でした。
そのエラーは、なくなりました。
(SELECT cos.*, res.* を簡易的に書いていただているのと勘違いして、
勝手に、
(SELECT costomer.*, costomer_reservation.* に書き換えていたことが原因でした。
これで、そっくり、書いていただいたものと同じになりました。

このエラーもなくなりました。

[退会済みユーザー]

次に、また新しいエラーがでました。
「SQLException:Column ' costomer_reservation.reservation_id' not found."」
です。

カラム名を確認中です。

[退会済みユーザー]

原因がかわりました。
ＳＱＬは無事に通過したようです。ありがとうございます。大変たすかりました。

どうやら、値を取り出すときに、テーブル名を
dbRes.getInt("costomer_reservation.reservation_id");
としていたので、エラーになったようです。

dbRes.getInt("res.reservation_id");
でやってみます。

[退会済みユーザー]

「SQLException:Column 'res.reservation_id' not found."」がまたでたので、

dbRes.getInt("reservation_id"); でやってみます。

やっぱり、UNIONを使うと、テーブル名.項目名で、
項目値を取り出せないみたいな予測です。

[退会済みユーザー]

上記エラーがとれました。
次のとりだそうとしている項目でエラーがでたので、
全部、頭のテーブル名をとれば、解決するようです。
「SQLException:Column 'res.company_id' not found.」

やはり、UNIONを使うと、取り出すときに、テーブル名.項目名 ではだめみたいですね。
同じ項目名がある場合は、どっちがとりだされるのかは、わかりませんが（重複エラーになるのかもしれません）。

とりあえず、ＳＱＬが解決したので、これで、
項目値の取り出し時に、全部、取り出せるように、頭のテーブル名を削除してやってみます。

UNIONは、その面で、不便ですね。＝私が言っていることが本当なら。

[退会済みユーザー]

抽出結果が、予約日の大きい順で表示されているところまでは確認できました。
ただ、他の会社のレコードも入っているので、ＳＱＬへ、
+ " (costomer.compay_id = " + iParaCompay_id + ")" // 会社ID
を追加して、やってみみます。

[退会済みユーザー]

上記で試したところ、
要求の仕様通りに、抽出されることが確認できました。
本当にありがとうございました。
一人では、とてもこのようなＳＱＬは作れませんでした。
繰り返しになりますが、ありがとうございました。クローズさせていただきます。

[hirohiro]

うまくいったようで良かったです。

>UNIONを使うと、抽出結果の値を取り出すときに、「costomer_reservation.reservation_date 」とテーブル指定ができなくて、「reservation_date 」という項目で取り出さないといけないので、同じ名前の項目があると、取り出せないですよね？

なるほど、今やっとこれの意味が理解できました。
JOINで繋いだから「compay_id」が２つあって、これをプログラム側で抽出できない、UNIONにすると各クエリを括弧で括るのでテーブルのエイリアスも統合されるので個別参照もできないってことですね。
selectでres.*のようにせずに必要なものだけ１つづつ書くか、ちょっと余計で格好悪いですが「SELECT cos.*, res.*,cos.compay_id as commonid」のように参照用のIDを追加(上下両方に必要)するかとかで解決できるかも知れません。

[退会済みユーザー]

なるほど、回避策ありがとうございます。
そうなんですよ、ＵＮＩＯＮは、便利？なくっつきＳＱＬなのですが、
いざ、項目値を取り出すときに、複数のテーブル内に同じ名前があると、
とりだせないのです。という、致命的な不便さが。
でも、上記の回避策でなんとなりそうですね。
なのですが、各テーブルのほぼ全部の項目２０個ほどを取り出すので、
asで逃げるのは、かなりくるしいです。＝項目数的に。
テーブルで、同じ名前の物だけ、ＡＳを付ければ、いいのかもしれませんね。

本当に、今回はありがとうございました。
おかげで、無事に、完成することができました。
心より感謝いたします。失礼します。