So I want to get all the antenas that have the band 800 and the band 900 and DO NOT have the band 1500 and DO NOT have the band 2500 with these two tables:
Table antenas:
id ------------- antena_1 antena_2 antena_3 antena_4 antena_5
Table bands:
antena_id band -------------------------- antena_1 800 antena_1 900 antena_1 1500 antena_1 2500 antena_2 800 antena_2 900 antena_2 1500 antena_3 800 antena_3 900 antena_3 1500 antena_3 2500 antena_4 800 antena_4 900 antena_5 800 antena_5 1500 antena_6 800 antena_7 800 antena_7 900 antena_7 3500
So the query has to return the antenas: antena_4 and antena_7
I have tried this but it does return antenas that do not have the 1500 bands:
SELECT * FROM antenas INNER JOIN bands ON antenas.id = bands.antena_id WHERE
NOT EXISTS (
SELECT antena_id FROM bands AS innerBands
WHERE innerBands.antena_id = antenas.id AND (
bands.band = "1500" OR bands.band = "2500"
)
)
AND band = "800" AND band = "900"
GROUP BY antenas.id
ORDER BY antenas.id ASC
I wonder if the NOT EXISTS does anything at all sinde its in a WHERE and I think I may have to use HAVING as it is a joint table.
I’m pretty clueless at this point after trying lots of stuff.
Answer
Use aggregation:
select antenna_id
from bands
group by antenna_id
having sum(band = 800) > 0 and
sum(band = 900) > 0 and
sum(band = 1500) = 0 and
sum(band = 2500) = 0;