0

I have 3 queries which I want to merge in one with the help of joins but it is not showing right result

from two tables I am taking data in which consphoneno is common, so one table has more than one row in which thre is a column tstime which is store current time of data saved so I want to select the latest row from that table and other table is having only row so from there I can fetch data the issue is when I am puting order by to get limit it gives wrong result

my table1

my table2

what I have done is

individually the three queries are 

 select consphoneno,billamt,cashbackamt,billno,businessname from cashbackdispdets a where consphoneno='8126815643'  order by tstime desc limit 1

select sum(billamt) billamtTotal,sum(cashbackamt) cashbackamtTotal from cashbackdispdets where consphoneno ='8126815643' 

select curyearpurchasedamt,curyearcashbackamt from cashbackdisp where consphoneno='8126815643'

What I have done is 

    select a.consphoneno,a.billamt,a.cashbackamt,a.billno,a.businessname, sum(a.billamt) billamtTotal,sum(a.cashbackamt) cashbackamtTotal, 
b.curyearpurchasedamt,b.curyearcashbackamt from cashbackdispdets a,cashbackdisp b where a.consphoneno='8126815643' order by a.tstime desc limit 1

but it is showing the wrong result

Please check third Image where I am getting wrong result

I want to getlatest row from table one but it is taking first one only

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
  • Please don't use Old comma based Implicit joins and use Modern [Explicit JOIN based syntax](https://stackoverflow.com/q/5654278/2469308) Also, no images please. They are hard to read and reproduce. – Madhur Bhaiya Oct 03 '19 at 07:09
  • I have uploaded Image just for better understanding that's it –  Oct 03 '19 at 07:10
  • This is a very common problem. I have added [tag:greatest-n-per-group] tag to the question. You can get a plethora of techniques here: https://stackoverflow.com/questions/tagged/greatest-n-per-group?tab=Votes Also, regarding images, please read: [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/q/333952/2469308) – Madhur Bhaiya Oct 03 '19 at 07:14
  • @MadhurBhaiya but still the issue is I am not getting the latest row from the table 1 –  Oct 03 '19 at 07:33
  • Have you tried the solutions given in the duplicate marked link ? If still not working, please do as it is mentioned here: https://meta.stackoverflow.com/q/333952/2469308 Remove images; give sample data as text, with proper expected output. Set up a fiddle. Edit the question with these details properly; once done, ping me, and we can reopen the question and community will be able to answer it as well. – Madhur Bhaiya Oct 03 '19 at 07:36

0 Answers0