Mysql – Finding a record that satisfies 2 exact dates

Fairly new to mysql & the stack overflow community. – I have a question that’s been bugging me.

I’m trying to return the results of individuals that have taken out a book on 2 specific dates. This is my effort so far;

SELECT borrower.fname, borrower.lname
FROM borrower, loan
WHERE borrower.cardno = loan.cardno
AND loan.dateout = DATE '2019-01-01'
AND loan.dateout = DATE '2018-02-01';

I’m not sure why I’m not getting any results – When I run the query with just one date it comes up with results. I can see from these results that there are definitely records that should satisfy the criteria, I’m not sure where I’m going wrong.

Can anyone pinpoint where I’m going wrong? I thought it looked quite straightforward but it’s driving me crazy.

Thanks

Answer

One option uses exists twice:

select b.*
from borrower b
where
    exists (select 1 from loan l where l.cardno = b.cardno and l.dateout = date '2019-01-01')
    and exists (select 1 from loan l where l.cardno = b.cardno and l.dateout = date '2018-02-01')

With an index on loan(cardno, dateout), this should be an efficient solution.

Alternatively, you can use aggregation:

select b.fname, b.lname
from borrower b
inner join loan l on l.cardno = b.cardno
where l.dateout in (date '2019-01-01', date '2018-02-01')
group by b.fname, b.lname
having
    max(case when l.dateout = date '2019-01-01' then 1 end) = 1
    and max(case when l.dateout = date '2018-02-01' then 1 end) = 1