Click here to Skip to main content
14,545,210 members
Rate this:
Please Sign up or sign in to vote.
See more:
This is my query

select  cstdetails.loandate, cstdetails.cstname, cstdetails.loanamt,
cstduebill.cstname,cstduebill.loanslno,  cstduebill.loanamt, 
cstduebill.billdate, cstduebill.totdueamt 
from cstdetails where cstdetails.loandate between '2020-05-20' and '2020-05-20' 
LEFT JOIN cstduebill ON cstdetails.cstname = cstduebill.cstname  WHERE rectno IN(
SELECT max(rectno) FROM cstduebill WHERE billdate between '2020-05-20' and '2020-05-20' 
GROUP BY loanslno) 
AND billdate between '2020-05-20' and '2020-05-20' ORDER BY loanslno


but error is came


Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'LEFT'.
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'AND'.


please any one solve my problem

What I have tried:

Left join error  Incorrect syntax near the keyword 'LEFT'.
Posted
Updated 20-May-20 0:47am

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

At the first look...

Striked content is redundant!
select  cstdetails.loandate, cstdetails.cstname, cstdetails.loanamt,
cstduebill.cstname,cstduebill.loanslno,  cstduebill.loanamt, 
cstduebill.billdate, cstduebill.totdueamt 
from cstdetails where cstdetails.loandate between '2020-05-20' and '2020-05-20' 
LEFT JOIN cstduebill ON cstdetails.cstname = cstduebill.cstname  WHERE rectno IN(
SELECT max(rectno) FROM cstduebill WHERE billdate between '2020-05-20' and '2020-05-20' 
GROUP BY loanslno) 
AND billdate between '2020-05-20' and '2020-05-20' ORDER BY loanslno


BTW: Please, use table aliases: SQL Aliases[^]

SELECT cd.loandate, cd.cstname, cd.loanamt,
cb.cstname, cb.loanslno,  cb.loanamt, cb.billdate, cb.totdueamt 
FROM cstdetails  cd 
LEFT JOIN cstduebill cb ON cd.cstname = cb.cstname
WHERE rectno IN(
    SELECT max(rectno)
    FROM cstduebill
    WHERE billdate between '2020-05-20' and '2020-05-20' 
    GROUP BY loanslno) 
AND billdate between '2020-05-20' and '2020-05-20'
AND cd.loandate between '2020-05-20' and '2020-05-20' 
ORDER BY loanslno
   
v3
Comments
Boopalslm 20-May-20 6:52am
   
Yes, I am using this query already, but how to get as on date
Cstdetails
Maciej Los 20-May-20 7:09am
   
"Yes, I am using this query already..."
Does it work? If it works, you should accept my answer as a solution (green button).

"... but how to get as on date"
This is another question (not originally posted!)...
Boopalslm 20-May-20 7:12am
   
Sir but how to get as on date cstdetails report
Maciej Los 20-May-20 7:18am
   
If you want to get today's data:
WHERE billdate between CONVERT(DATE, GETDATE()) and CONVERT(DATE, GETDATE())
Boopalslm 20-May-20 7:21am
   
where i am used your query, please combine above your query with my below query

SELECT cd.loandate, cd.cstname, cd.loanamt,
cb.cstname, cb.loanslno, cb.loanamt, cb.billdate, cb.totdueamt
FROM cstdetails cd
LEFT JOIN cstduebill cb ON cd.cstname = cb.cstname
WHERE rectno IN(
SELECT max(rectno)
FROM cstduebill
WHERE billdate between '2020-05-20' and '2020-05-20'
GROUP BY loanslno)
AND billdate between '2020-05-20' and '2020-05-20'
ORDER BY loanslno
Maciej Los 20-May-20 8:15am
   
Please, see updated answer.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100