0

I am trying to get a resulting column of the initial month an ID was created where multiple table JOINs are needed in MySQL Workbench.

SET @in_month = '0';

SELECT 
    ca.id
FROM capital.user ca
    JOIN 
    capital.user_account cd on ca.id = cd.user_id
    JOIN
    capital.transaction ct on cd.user_id = ct.user_account_id
    JOIN
    capital.transaction_event ce on ct.id = ce.auth_entry_id 

  @in_month = month(ce.created) WHERE ce.message = 'Approved'

Group by id;

I get Syntax error: '@in_month' (at text suffix) is not valid input at this position on line 17, any ideas of what I might be doing wrong? I don't have a lot of experience with SQL

Fabsklo
  • 73
  • 1
  • 2
  • 8

1 Answers1

0

you are missing semicolon after variable declaration & and on join condition 

    SET @in_month = '0';

    SELECT 
        ca.id
    FROM capital.user ca
    JOIN capital.user_account cd on ca.id = cd.user_id
    JOIN capital.transaction ct on cd.user_id = ct.user_account_id
    JOIN capital.transaction_event ce on ct.id = ce.auth_entry_id  and 
         @in_month = month(ce.created) 
    WHERE ce.message = 'Approved'
    Group by id;
Ankit Agrawal
  • 2,426
  • 1
  • 13
  • 27
  • with the semicolons I get "error : invalid input at this position" at lines 1 and 17 – Fabsklo May 29 '18 at 09:34
  • you are also missing the semicolon at declare statement thats why its throwing an error . i have updated code .. look at them – Ankit Agrawal May 29 '18 at 09:40
  • Syntax error: 'Declare' (declare) is not valid input at this position on line 1 and Syntax error: '@in_month' (at text suffix) is not valid input at this position on line 17 – Fabsklo May 29 '18 at 09:44
  • you dont have to do this DECLARE @in_month INT; in your code...just check it out – Ankit Agrawal May 29 '18 at 09:47
  • I see what you mean Ankit, I have removed line 1. Unfortunately I still get the same syntax error not valid input at this position on line 17.. now line 16. @in_moth = month(ce.created) WHERE ce.message = 'Approved' – Fabsklo May 29 '18 at 10:23