I am using Mysql database and having a problem with inner join. I am using aggregate function max(dn.on_date) in the query to select only recent date rows. this is working fine when rows are found with matching criteria but when where clause condition becomes false it 1 row is returned containing NULL value for all columns. I want to ignore that row to be returned as result.
Table structures are as follows : view table structure and relation
my query is like :
SELECT d.first_name, d.last_name, d.mobile, dn.don_amount as amount, max(dn.on_date) as on_date
FROM donors d
inner join donation dn on d.mobile = dn.don_id
WHERE LOWER(first_name) LIKE LOWER('dinesh%') order by d.mobile asc LIMIT 0,4
Unwanted Results is returned like this :
view results
I want that it should not return anything when where condition is not matched
I have tried like this but it doesn't work and result is same as previous
SELECT d.first_name, d.last_name, d.mobile, dn.don_amount as amount, max(dn.on_date) as on_date
FROM donors d
inner join donation dn on d.mobile = dn.don_id
WHERE d.mobile IS NOT NULL AND LOWER(first_name) LIKE LOWER('dinesh%') order by d.mobile asc LIMIT 0,4
you used aggregate function but not seen group by in your query
SELECT d.first_name, d.last_name, d.mobile, dn.don_amount as amount, max(dn.on_date) as on_date
FROM donors d
inner join donation dn on d.mobile = dn.don_id
WHERE LOWER(first_name) LIKE 'dinesh%'
group by d.first_name, d.last_name, d.mobile, dn.don_amount
order by d.mobile asc
LIMIT 0,4