SQLでのデータ取得方法について

Question

以下のような、学校のクラスの生徒を保持しているテーブルと、履歴を保持するテーブルがあるとします

■現クラステーブル：
今現在のクラスの生徒の情報を保持しているテーブル

■旧クラステーブル：
クラスに生徒が増えると登録されるテーブルで、
履歴を管理する番号と生徒の番号を保持しているテーブル

現クラステーブル

クラスID	生徒連番	生徒名
1000	1	田中一郎
1000	2	佐藤二郎
1000	3	鈴木三郎

旧クラステーブル

クラスID	クラス履歴番号	生徒連番	生徒名
1000	1	1	田中一郎
1000	2	1	田中一郎
1000	2	2	佐藤二郎

上記のようなデータが存在した時、
以下の結果を返すSQLを作成したいです。
（現在のクラスの生徒と、ある時点のクラスの生徒の情報を併せて取得したい）

旧クラスID	旧クラス履歴番号	旧生徒連番	旧生徒名	現クラスID	現生徒連番	現生徒名
1000	2	1	田中一郎	1000	1	田中一郎
1000	2	2	佐藤二郎	1000	2	佐藤二郎
		1000	3	鈴木三郎

取得できた同じレコードの「旧生徒連番と旧生徒名を連結したもの」と、「新生徒連番と新生徒名を連結したもの」を比較し、
違っていれば、追加/変更があったと検知したいです。
比較自体はSQLでやる必要はないので、データを取得できるところまで実現できれば問題ありません。

以下のSQLを作成してみましたが、意図した結果にはなりませんでした。
ご助言いただける方、コメントをお願い致します。

SELECT
    A.クラスID,
    A.クラス履歴連番,
    A.生徒連番,
    A.生徒名,
    B.クラスID,
    B.生徒連番,
    B.生徒名
FROM
    旧クラステーブル A,
    現クラステーブル B
WHERE
	A.クラスID = B.クラスID and
	B.クラス履歴番号 = '2'

Answer 1

ノーチェックですが

SELECT
    A.クラスID,
    A.クラス履歴連番,
    A.生徒連番,
    A.生徒名,
    B.クラスID,
    B.生徒連番,
    B.生徒名
FROM
    現クラステーブル B LEFT JOIN
    (SELECT * FROM 旧クラステーブル WHERE クラス履歴番号 = '2') A ON A.クラスID = B.クラスID

でできませんか

Comments

[uruban]

早々の回答ありがとうございます
確認してみます

[uruban]

求めている結果は求められませんでしたが、コメントありがとうございました

[rururu3]

よく見たらちょっと間違ってましたね・・・チェック大切ですねｗ

Answer 2

取得できた同じレコードの「旧生徒連番と旧生徒名を連結したもの」と、「新生徒連番と新生徒名を連結したもの」を比較し、違っていれば、追加/変更があったと検知したいです。

求める結果を出すには結合する必要があります。
結合する＝等しいとなるので、その後の比較には意味がありません。
生徒連番で結合するなら、生徒名で比較だし、生徒名で結合するなら生徒連番という片側での比較でしょう。

取り敢えずそれは置いといて、旧クラステーブルの最新（クラス履歴番号が最大のもの）と現クラステーブルの突合について、

SELECT
    A.クラスID, A.クラス履歴連番, A.生徒連番, A.生徒名
  , B.クラスID, B.生徒連番, B.生徒名
FROM
    現クラステーブル B full join (
      select * from 旧クラステーブル a0
      where 旧クラス履歴番号=(
             select max(旧クラス履歴番号) from 旧クラステーブル where クラスID=a0.クラスID
            ) 
    ) A on B.クラスID=A.クラスID and B.生徒連番=A.生徒連番 

全外部結合で取り出せば、削除も判断できます。（不要なら、left joinに変更して下さい）

性能を考慮した別パターン

SELECT
    A.クラスID, A.クラス履歴連番, A.生徒連番, A.生徒名
  , B.クラスID, B.生徒連番, B.生徒名
FROM
    現クラステーブル B full join (
      select * from 旧クラステーブル a0
      where (クラスID, 旧クラス履歴番号) in (
             select クラスID, max(旧クラス履歴番号) from 旧クラステーブル group by クラスID
            ) 
    ) A on B.クラスID=A.クラスID and B.生徒連番=A.生徒連番 

Comments

[uruban]

ありがとうございました。求める結果が得られました。
連結という言葉が誤解を与えてしまったかもしれません。

私がイメージしていたのは、
生徒連番と生徒名を、concatして、1田中という文字列にして、
比較すれば、連番は同じだが、名前が違うというケースでも、
差分を検知できると考えました。

[sazi]

別パターンを追記しました。
インデックスの状況にもよると思いますが、性能に差が出るかもしれません。