InnerJoinでSQLをまとめたい

Question

前提・実現したいこと

下記のテーブルを基にそれぞれのSELECT文を作成しました。
これを①＋②ー③のようにまとめ、amountがマイナスのものを削除したレコードを取り出したいのですが、うまくできません。

①select MAX(id) AS `id` from t_transactions group by tran_grp;

②select id from t_transactions where settle_grp IS not null;

③select settle_grp from t_transactions where settle_grp IS NOT NULL group by settle_grp;

id	payment_date	amount	tran_grp	settle_grp
132	2019/7/1	       20	 132	      NULL
133	2019/7/1	      -20	 132	      NULL
134	2019/7/1	       20	 132	      NULL
135	2019/10/1	     1000	135	      NULL
137	2019/10/1	     1000	137	      NULL
138	2019/10/1	    -1000	137	      NULL
139	2019/10/1	     1000	137	      NULL
140	2019/10/1	    -1000	137	       139
141	2019/11/1	      300	137	       139
142	2019/11/3	      700	137	       139
143	2019/11/3	     -700	137	       142
144	2019/11/3	      300	137	       142
145	2019/11/10	     400	137	       142

該当のソースコード

#①＋②ー③の結果

id	payment_date	amount	tran_grp	settle_grp
134	2019/7/1	       20	 132	      NULL
135	2019/10/1	     1000	135	      NULL
140	2019/10/1	    -1000	137	       139
141	2019/11/1	      300	137	       139
143	2019/11/3	     -700	137	       142
144	2019/11/3	      300	137	       142
145	2019/11/10	     400	137	       142

#最終的にほしいレコード

id	payment_date	amount	tran_grp	settle_grp
134	2019/7/1	       20	 132	      NULL
135	2019/10/1	     1000	135	      NULL
141	2019/11/1	      300	137	       139
144	2019/11/3	      300	137	       142
145	2019/11/10	     400	137	       142

試したこと

SQLを分割して、InnerJoinでつなげようと思いましたが下記エラーがでてしまいました。
どうぞよろしくお願いいたします。

#①＋②

    SELECT MAX(id) AS `id` from t_transactions group by tran_grp AS join1
INNER JOIN select `id` from t_transactions where settle_grp IS not null AS join2
        ON join2.id = join1.id;

発生している問題・エラーメッセージ

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS join1 INNER JOIN select `id` from t_transactions where settle_grp IS not null' at line 1

補足情報（FW/ツールのバージョンなど）

mysql 5.7

追記　①＋②ー③までをサブクエリを利用して作成できました。ここからマイナス分を削除しようと思います。

SELECT *
FROM t_transactions
WHERE t_transactions.id = (
        SELECT MAX(id)
        FROM t_transactions AS tmp
        WHERE t_transactions.tran_grp = tmp.tran_grp
    ) 
    or settle_grp IS not null 
    and id NOT IN (
	select settle_grp from t_transactions where settle_grp IS NOT NULL group by settle_grp
);

Comments

[m.ts10806]

「うまくできませんでした」というのは何も伝わらない表現となります。
具体的に起きた現象、エラーなどを記載してください。
https://teratail.com/help/question-tips#questionTips3-4-1

[no1knows]

失礼いたしました。エラーメッセージを追記しました。

Answer 1

対象のテーブルが1つであるときに自身にむけてinnerjoinするのはあまりいいやり方とは言えません。

「うまくいかない」コードもサブクエリを利用されたほうが良いでしょう（エラーですし）
エラーなくすだけならこう。

select
    *
from
    (
        SELECT
            MAX(id) AS `id`
        from
            t_transactions
        group by
            tran_grp
    ) AS join1
    INNER JOIN
        (
            select
                id
            from
                t_transactions
            where
                settle_grp IS not null
        ) AS join2
    ON  join2.id = join1.id
;

※フォーマットサービスを利用しました

エラー確認については構文チェックサービスを利用しました。

---

あとはこれで「想定通りの結果がでるか」ですけど、たぶんでない気がします。
SQLとかもそうですけど、一番内側から実行されていくものなので、順番を間違えると思わぬ結果が返ってきます。

「個々に動いているSQLをまとめる」というより、「どういう条件でデータを絞り込むか」という観点で考えてはいかがでしょうか？

Comments

[no1knows]

よい考え方をいただきありがとうございます。
まだ①＋②ー③ができた段階ですが、徐々に期待するものができつつあります。

[no1knows]

直接的な回答というより、考え方を教えていただいたので非常にためになったという視点でべすとあんさーとさせていただきます。

[m.ts10806]

ヒントになったようでなによりです

Answer 2

create table tbl
(id int,payment_date date,amount int,tran_grp int,settle_grp int null);

insert into tbl values 
(132,'2019-07-01',   20,132,NULL),
(133,'2019-07-01',  -20,132,NULL),
(134,'2019-07-01',   20,132,NULL),
(135,'2019-10-01', 1000,135,NULL),
(137,'2019-10-01', 1000,137,NULL),
(138,'2019-10-01',-1000,137,NULL),
(139,'2019-10-01', 1000,137,NULL),
(140,'2019-10-01',-1000,137, 139),
(141,'2019-11-01',  300,137, 139),
(142,'2019-11-03',  700,137, 139),
(143,'2019-11-03', -700,137, 142),
(144,'2019-11-03',  300,137, 142),
(145,'2019-11-10',  400,137, 142);

(1)134,135,145
(2)140,141,142,143,144,145
(3)139,142

で、(1)と(2)の集合から(3)を除いて
→134,135,140,141,143,144,145

内、140,143はamountがマイナスなので除いて
→134,135,141,144,145

ということですね？

select * from tbl as t1
inner join (
select max(id) as id from tbl group by tran_grp 
union select id from tbl where settle_grp is not null) as t2 using(id)
where amount >= 0 
and not exists(
select 1 from tbl 
where settle_grp is not null group by settle_grp having settle_grp = t2.id)

Comments

[no1knows]

ありがとうございます。こちらで期待する回答を得ることができました！

ベストアンサーは、最初に考え方などのヒントを教えてくれたmts10806さんにさせていただきます。

Answer 3

#最終的にほしいレコードと質問の条件について合致しない部分があるので、#最終的にほしいレコードに合わせる形でSQLを組んでみました。
※②select id from t_transactions where settle_grp IS not null;を含めると欲しい形にはなりません。

select join1.* 
from t_transactions join1
     inner join (
        select max(id) as id from t_transactions
        where amount >= 0
        group by tran_grp, payment_date
     ) join2
     on join1.id=join2.id

③の条件がIDに関するものというのに気付けなかった。
解決したようですけど、取り敢えず修正版です。

select join1.* 
from t_transactions join1
     inner join (
        select max(id) as id from t_transactions
        where amount >= 0
        group by tran_grp, payment_date
     ) join2
     on join1.id=join2.id
where join1.id not in (
        select settle_grp from t_transactions where settle_grp IS NOT NULL
       )
order by id

Comments

[no1knows]

ありがとうございます。
こちらを実行したところ残念ながらID139が残ってしまいました。