SQL like but not like

Currently having some issues with my script.

The question goes like this ‘Show all cars which participated in event 201504 but did not participate in event 201504. So if they were not involved in event 01 they should not be displayed. Which they currently do.

I made my query as following but can’t get any further.

select *
from autos a
join deelnemers d on a.autonr = d.autonr
join evenementen e on d.evenementnr = e.evenementnr
where e.evenementnr = '201504'
    and e.evenementnr != '201501'                               
order by bouwjaar desc

Answer

One method is aggregation – but this requires you to list the columns you want to display from the autos table:

select a.id, a.name
from autos a
inner join deelnemers d on a.autonr = d.autonr
inner join evenementen e on d.evenementnr = e.evenementnr
where e.evenementnr in (201504, 201501)
group by a.id, a.name
having 
    max(case when e.evenementnr = 201504 then 1 else 0 end) = 1
    and max(case when e.evenementnr = 201501 then 1 else 0 end) = 0

Otherwise, you can also use exists and not exists. This is longer to type, but might perform equally well, or better than the group by/having solution:

select a.*
from autos a
where 
    exists (
        select 1
        from deelnemers d
        inner join evenementen e on d.evenementnr = e.evenementnr
        where a.autonr = d.autonr and e.evenementnr = 201504
    )
    and not exists (
        select 1
        from deelnemers d
        inner join evenementen e on d.evenementnr = e.evenementnr
        where a.autonr = d.autonr and e.evenementnr = 201501
    )