Query runs on mysql, but fails when put in Query Checker

Question

CREATE TABLE T1(client_id int, Loancount int, PRIMARY KEY (client_id));


INSERT INTO  T1(client_id, Loancount)
SELECT DISTINCT C.client_id, count(loan_id) as LoanCount
FROM CLIENT  as C, DISP as D, LOAN as L
WHERE (C.client_id=D.client_id) AND (D.account_id=L.account_id)
group by  C.client_id;

CREATE TABLE T2 (client_id int, Trancount int, PRIMARY KEY (client_id));
INSERT INTO  T2(client_id, Trancount)
(SELECT DISTINCT C.client_id, count(trans_id) as Trancount
FROM CLIENT  as C, DISP as D, TRANSACTION as T
WHERE (C.client_id=D.client_id) AND (D.account_id=T.account_id)
group by  C.client_id) ;


SELECT DISTINCT T1.client_id, IF(Trancount>=LoanCount,Trancount,LoanCount) as Maxcount
FROM T1,T2
WHERE T1.client_id=T2.client_id;

DROP Table T1;
DROP Table T2;

When I try running this query here(https://www.eversql.com/sql-syntax-check-validator/), it says an error exists around. INSERT INTO T1. The code compiles though.

Error Message:"You have an error in your SQL syntax; it seems the error is around: 'INSERT INTO T1(client_id, Loancount) SELECT DISTINCT C.client_id, count(lo' at line 6"

Please ALWAYS show us the complete error message. A summary is never really any use to us — RiggsFolly, Apr 18 '20 at 12:45
Could it be `SELECT DISTINCT C.client_id, count(L.loan_id) as LoanCount` — RiggsFolly, Apr 18 '20 at 12:47
@RiggsFolly We are supposed to find the table separately and then compute the max. If I join I assume the person has had a transaction and has a loan too. — Pikachu1998, Apr 18 '20 at 12:49
What do you think `FROM CLIENT as C, DISP as D, LOAN as L` is doing, but in a less predictable way — RiggsFolly, Apr 18 '20 at 12:50
Also there are various types of JOIN https://www.dofactory.com/sql/join — RiggsFolly, Apr 18 '20 at 12:51
PS You dont need the `DISTINCT client_id ....` if you have the `Group BY client_id` — RiggsFolly, Apr 18 '20 at 12:53

slaakso · Answer 1 · 2020-04-18T15:14:32.383

Are you pasting all the queries at once to the query checker? Most likely it accepts only one query at the time.

You can do the same with single query. No need for tables:

SELECT q.client_id, IF(max(q.Trancount)>=max(q.LoanCount),max(q.Trancount),max(q.LoanCount))
FROM (
  SELECT C.client_id, 0 as LoanCount, count(T.trans_id) as Trancount
  FROM CLIENT C, 
    JOIN DISP D ON D.client_id=C.client_id
    JOIN TRANSACTION T ON T.account_id=D.account_id
  group by  C.client_id
  UNION
  SELECT C.client_id, count(L.loan_id) as LoanCount, 0 as Trancount
  FROM CLIENT C, 
    JOIN DISP D ON D.client_id=C.client_id
    JOIN LOAN as L ON L.account_id=D.account_id
  group by  C.client_id
) as q
group by  Q.client_id

Btw, you should use temporary tables in cases where you just create table and then drop it after a query.

Query runs on mysql, but fails when put in Query Checker

1 Answers1