-1
SELECT *
FROM student as std LEFT JOIN
     (SELECT * FROM billing WHERE `paid` < '4' ) AS bill
    ON bill.reg_id = std.reg_id
GROUP BY bill.reg_id
ORDER BY bill.id

Here student table is main table and its primary key is foreign key in billing table. There are multiple records in billing table with student reg_id .

I want latest billing record with limit 1 and no limit on student table.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Nilesh Pande
  • 25
  • 1
  • 8
  • 1
    Please provide sample data and desired results. – Gordon Linoff Jun 12 '18 at 12:12
  • What you have include in the question has nothing to do with php or mysqli. – Dragonthoughts Jun 12 '18 at 12:13
  • Trying to set limit on billing table. – Nilesh Pande Jun 12 '18 at 12:15
  • SELECT * FROM student as std LEFT JOIN (SELECT * FROM billing WHERE `paid` < '4' LIMIT 1 ORDER BY id DESC ) AS bill ON bill.reg_id = std.reg_id – Nilesh Pande Jun 12 '18 at 12:17
  • SELECT reg_id, id ,name,paid,amount_tobe_pay,paid_ammount,date,note FROM ( SELECT reg_id, MAX(id), id ,name,paid,amount_tobe_pay,paid_ammount,date,note FROM billing GROUP BY reg_id DESC) as ids ORDER BY reg_id; I've tried this but not able to join with student table. This query gives me output but mysql shows error. – Nilesh Pande Jun 15 '18 at 17:44
  • select * from student as std left join (select MAX(id) from billing WHERE billing.paid < '4') bill on bill.reg_id = std.reg_id . Also tried this but billing record gives null. – Nilesh Pande Jun 15 '18 at 17:58

2 Answers2

0

Try This Query, I think it should work

SELECT *
FROM student as std LEFT JOIN
 (SELECT * FROM billing WHERE `paid` < '4' group by reg_id order by id desc) 
 AS bill
ON bill.reg_id = std.reg_id
Ajith
  • 258
  • 1
  • 7
  • You can't use `SELECT * ... GROUP reg_id` and trust the results are correct within MySQL.. https://www.psce.com/en/blog/2012/05/15/mysql-mistakes-do-you-use-group-by-correctly/ .. Only when column reg_id has a PRIMARY or UNIQUE key the `SELECT *` can be used because the other columns are functional dependent on the reg_id column – Raymond Nijland Jun 12 '18 at 12:56
  • Thanks man it works. I've already set primary key and foreign key to bill table. – Nilesh Pande Jun 13 '18 at 06:10
  • Still not getting desired result. want single last record of billing table. – Nilesh Pande Jun 13 '18 at 13:17
  • You need Final Output to single Record.? Can please post your desired output sample so that we can help you. I believe when you use group by regid it will pick only one record for each reg_id – Ajith Jun 15 '18 at 11:52
  • SELECT reg_id, id ,name,paid,amount_tobe_pay,paid_ammount,date,note FROM ( SELECT reg_id, MAX(id), id ,name,paid,amount_tobe_pay,paid_ammount,date,note FROM billing GROUP BY reg_id DESC) as ids ORDER BY reg_id; I've tried this but not able to join with student table. This query gives me output but mysql shows error. – Nilesh Pande Jun 15 '18 at 17:44
0

I'm assuming there's a column called billing_date, you'll have to update query with the correct timestamp. This is giving each billing record a rank based on its recency then pulling back only the most recent. It's assuming mysql 8.0, you can mimic this in earlier version.

select
* 
from student std
left join (select
*, row_number() over (partition by reg_id order by billing_date desc) rn
from billing) bill on bill.reg_id = std.reg_id and rn=1
JoeWilson
  • 156
  • 1
  • 5
  • You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(partition by reg_id order by billing_date desc) rn from billing) bill on bill.r' at line 1 – Nilesh Pande Jun 13 '18 at 05:43
  • I want all record of student but only one record of bill table of each student. – Nilesh Pande Jun 13 '18 at 05:44
  • I was able to get this working with dummy data on mysql 8.0 and it did what you're looking for. You may have to add your business logic around the 'paid' <4, but it will only return 1 bill row per student. Your error may be because you're using an older version of mysql, please advise your mysql version. – JoeWilson Jun 14 '18 at 02:06
  • I'm using XAMPP for Windows 5.6.24. and its Server version: 10.1.16-MariaDB - mariadb.org binary distribution. – Nilesh Pande Jun 14 '18 at 07:21
  • row_number was added in Maria 10.2, so may not be a help there. . Look at the answers here for work around ideas: https://stackoverflow.com/questions/1895110/row-number-in-mysql – JoeWilson Jun 14 '18 at 13:39
  • SELECT reg_id, id ,name,paid,amount_tobe_pay,paid_ammount,date,note FROM ( SELECT reg_id, MAX(id), id ,name,paid,amount_tobe_pay,paid_ammount,date,note FROM billing GROUP BY reg_id DESC) as ids ORDER BY reg_id; I've tried this but not able to join with student table. This query gives me output but mysql shows error. – Nilesh Pande Jun 15 '18 at 17:44