Join two tables with WHERE clause with two conditions and not having a condition

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;

Leave a Reply

Your email address will not be published. Required fields are marked *