problem in self join query (1 Viewer)

royalhishighness

New member
Local time
Today, 15:52
Joined
Dec 29, 2013
Messages
6
HI,
Below query is working fine in SQL Server but when I am trying to run in ms access error message popup: syntax error (missing operator) in query expression 't1.[BankName]=mast.[BankName]
LEFT JOIN (SELECT DISTINCT (CMaster.cbankname) AS BankName, Count(*) AS BankSanctionTotal, Sum(CMaster.BankSanAmt) AS BankSanction FROM CMaster WHERE ((CMaster.BankSanAmt) Is Not Null) GROUP BY CMaster.cbankname) AS t2 ON t2.[BankName]=mast.[BankName'.

SELECT mast.[bankname], t1.[Total], t1.[SumOfTFCLoan], t2.[BankSanctionTotal], t2.[BankSanction]
FROM (SELECT DISTINCT (CMaster.cbankname) AS BankName FROM CMaster) AS mast
LEFT JOIN (SELECT DISTINCT (CMaster.cbankname) AS BankName, Count(*) AS Total, Sum(CMaster.TFCLoan) AS SumOfTFCLoan
FROM CMaster WHERE ((CMaster.TFCLoan) Is Not Null) GROUP BY CMaster.cbankname) AS t1 ON t1.[BankName]=mast.[BankName]
LEFT JOIN (SELECT DISTINCT (CMaster.cbankname) AS BankName, Count(*) AS BankSanctionTotal, Sum(CMaster.BankSanAmt) AS BankSanction FROM CMaster WHERE ((CMaster.BankSanAmt) Is Not Null) GROUP BY CMaster.cbankname) AS t2 ON t2.[BankName]=mast.[BankName]
order by mast.bankname;


when I am removing 2nd left join part it is working fine and the query is:
SELECT mast.[bankname], t1.[Total], t1.[SumOfTFCLoan]
FROM (SELECT DISTINCT (CMaster.cbankname) AS BankName FROM CMaster) AS mast
LEFT JOIN (SELECT DISTINCT (CMaster.cbankname) AS BankName, Count(*) AS Total, Sum(CMaster.TFCLoan) AS SumOfTFCLoan
FROM CMaster WHERE ((CMaster.TFCLoan) Is Not Null) GROUP BY CMaster.cbankname) AS t1 ON t1.[BankName]=mast.[BankName]
order by mast.bankname;
Thanks in advance.
 
Last edited:

JHB

Have been here a while
Local time
Today, 11:22
Joined
Jun 17, 2012
Messages
7,732
Only to find out what where the error is, what happens if you remove the 1. left join?
 

royalhishighness

New member
Local time
Today, 15:52
Joined
Dec 29, 2013
Messages
6
Thanks for your reply.
And sorry for my previous reply.
Result coming perfectly after removing 1st join
 

royalhishighness

New member
Local time
Today, 15:52
Joined
Dec 29, 2013
Messages
6
when I am having more then 2 joins in query error displays as syntax error (missing operator).
Please help
 

JHB

Have been here a while
Local time
Today, 11:22
Joined
Jun 17, 2012
Messages
7,732
Post a stripped version of you database with some sample data in it, (zip it because you haven't post 10 post yet.).
 

JHB

Have been here a while
Local time
Today, 11:22
Joined
Jun 17, 2012
Messages
7,732
This should work:
SELECT mast.[bankname], t1.[Total], t1.[SumOfTFCLoan], t2.[BankSanctionTotal], t2.[BankSanction]
FROM ((SELECT DISTINCT (CMaster.cbankname) AS BankName FROM CMaster) AS mast
LEFT JOIN (SELECT DISTINCT (CMaster.cbankname) AS BankName, Count(*) AS Total, Sum(CMaster.TFCLoan) AS SumOfTFCLoan
FROM CMaster WHERE ((CMaster.TFCLoan) Is Not Null) GROUP BY CMaster.cbankname) AS t1 ON t1.[BankName]=mast.[BankName])
LEFT JOIN (SELECT DISTINCT (CMaster.cbankname) AS BankName, Count(*) AS BankSanctionTotal, Sum(CMaster.BankSanAmt) AS BankSanction FROM CMaster WHERE ((CMaster.BankSanAmt) Is Not Null) GROUP BY CMaster.cbankname) AS t2 ON t2.[BankName]=mast.[BankName]
order by mast.bankname;
 

royalhishighness

New member
Local time
Today, 15:52
Joined
Dec 29, 2013
Messages
6
thanx alot.
but when i m trying to add more joins i m getting same error plz give me the exact solution for this.
 

JHB

Have been here a while
Local time
Today, 11:22
Joined
Jun 17, 2012
Messages
7,732
The differences are the parentheses "()".
SELECT mast.[bankname], t1.[Total], t1.[SumOfTFCLoan], t2.[BankSanctionTotal], t2.[BankSanction]
FROM ((SELECT DISTINCT (CMaster.cbankname) AS BankName FROM CMaster) AS mast
LEFT JOIN (SELECT DISTINCT (CMaster.cbankname) AS BankName, Count(*) AS Total, Sum(CMaster.TFCLoan) AS SumOfTFCLoan
FROM CMaster WHERE ((CMaster.TFCLoan) Is Not Null) GROUP BY CMaster.cbankname) AS t1 ON t1.[BankName]=mast.[BankName])
LEFT JOIN (SELECT DISTINCT (CMaster.cbankname) AS BankName, Count(*) AS BankSanctionTotal, Sum(CMaster.BankSanAmt) AS BankSanction FROM CMaster WHERE ((CMaster.BankSanAmt) Is Not Null) GROUP BY CMaster.cbankname) AS t2 ON t2.[BankName]=mast.[BankName]
order by mast.bankname;
 

Users who are viewing this thread

Top Bottom