Muhammad Usman Muhammad Usman - 3 months ago 10
SQL Question

Get Last Record of each donor

I am working on a BDS feature for a website in Laravel. Now I've tried asking this question twice here, but somehow I'm not able to ask it correctly, so I'll try to give it 1 last try.

I have 2 tables Donors and DonorRecords. What I want to to is to get all donors and their latest donation date only and only if they have not donated blood in last 3 month. I've tried it by both ways. Eloquent and Query.

Following is my query that I'm using:

SELECT donors.name, donors.mobile, donors.address, donors.blood_group, donors.area, MAX(donor_records.donation_date) AS d_date FROM donor_records JOIN donors ON donors.id = donor_records.id WHERE NOW() > DATE_ADD(donor_records.donation_date, INTERVAL 90 DAY)


Following are records in my database tables.

Donors:

enter image description here

DonorsRecord:

enter image description here

Now, But when I execute the query I only get the record with the donation date "2016-04-12" and not the other one. So can anyone help me with this 1 last time. It doesn't matter if it's from Eloquent or from DB side, both will work fine.

Answer
SELECT donors.Name FROM donors JOIN donorsRecord ON donors.id = donorsRecord.id
WHERE NOW() > DATE_ADD(donorRecord.donationDate, INTERVAL 3 MONTH)
group by donors.id;
Comments