CODEWITHSUNDEEP
mysqlsql
Aleix
Aleix

Reputation: 497

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.

Upvotes: 2

Views: 34

Answers (4)

Power Mouse
Power Mouse

Reputation: 1441

select b.antena_id, * from antena a
right outer join bands b on a.id = b.antena_id 
where b.band not in (1500, 2500)
    and b.band in (800, 900)

enter image description here

Upvotes: 0

ex4
ex4

Reputation: 2428

Or use inner query

SELECT distinct(antenna_id)
FROM bands
WHERE
   band in (800, 900)
   AND antenna_id NOT IN (select antenna_id from bands where band in (1500, 2500));

Upvotes: 0

The Impaler
The Impaler

Reputation: 48770

You can do:

select a.antena_id
from bands a
join bands b on b.antena_id = a.antena_id and b.band = 900
left join bands c on c.antena_id = a.antena_id and c.band = 1500
left join bands d on d.antena_id = a.antena_id and d.band = 1500
where a.band = 800
  and c.antena_id is null
  and d.antena_id is null

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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;

Upvotes: 2

Related Questions