help with sql quey

[pasin]

Can somebody help me with below sql query. It is showing an error and probably it is because of brackets. Thanks!


Error is: Syntax error (missing operator)

select clientt.clientid,loant.loanid
from (clientt
inner join loant on clientt.clientid=loant.clientid)
inner join
schedulet s1 on loant.loanid=s1.loanid
left join
(SELECT pay.loanid,  pay.paiddate FROM paymentt AS pay 
 INNER JOIN (SELECT max(paymentid) AS id FROM paymentt GROUP BY loanid)  AS [max] ON pay.paymentid=max.id) 
 AS t ON t.loanid=s1.loanid

[Minty]

Break each bit of it down and see if it runs.
Check this returns what you expect

SELECT pay.loanid, pay.paiddate FROM paymentt AS pay
INNER JOIN (SELECT max(paymentid) AS id FROM paymentt GROUP BY loanid) AS [max] ON pay.paymentid=max.id

If it does then this might work

SELECT  clientt.clientid,loant.loanid
FROM clientt
INNER JOIN loant on clientt.clientid=loant.clientid
INNER JOIN schedulet s1 on loant.loanid=s1.loanid
LEFT JOIN (SELECT pay.loanid,  pay.paiddate FROM paymentt AS pay 
INNER JOIN (SELECT max(paymentid) AS id FROM paymentt GROUP BY loanid)  AS [max] ON pay.paymentid=[max].id) 
 AS t ON t.loanid=s1.loanid

BTW - Max is a terrible choice of alias. I'm not sure I can think of a worse one other than maybe SELECT

[orange]

I have reformatted Minty's sql using the free PoorSQL

SELECT clientt.clientid
	,loant.loanid
FROM clientt
INNER JOIN loant
	ON clientt.clientid = loant.clientid
INNER JOIN schedulet s1
	ON loant.loanid = s1.loanid
LEFT JOIN (
	SELECT pay.loanid
		,pay.paiddate
	FROM paymentt AS pay
	INNER JOIN (
		SELECT max(paymentid) AS id
		FROM paymentt
		GROUP BY loanid
		) AS [max]
		ON pay.paymentid = [max].id
	) AS t
	ON t.loanid = s1.loanid

[Micron]

FYI trouble shooting tip - if you put the query in sql view then attempt to run it and it fails, the cursor usually goes just to the right of the offending part. Sometimes helps to zero in on the offending syntax.