How to exclude data in mySQL string?

Question

it's just an example, my real tables are much larger and more complicated.
CREATE TABLE Apples (
    Id1,
    Name,
    Id2,
)

CREATE TABLE Colors (
    Id2,
    ColorName
)

I want to include all apples instead of the green variety of ID1 = 3 (lets lay called Boscop)
How to write the WHERE clause to exclude green Boscop?
I think it's easy but for the moment I have no idea.
Thanks!

Gordon Linoff · Answer

I am guessing you want not exists:
select a.*
from apples a
where a.id1 = 1 and
      not exists (select 1
                  from colors c
                  where a.id2 = c.id2 and c.color = 'green'
                 );

akshaivk · Answer

One simple method is as below. Select the relevant rows from the table instead of selecting all. Hope this helped.

select * from Colors t1 ,Apples t2 where t1.id2=t2.id2(+) and t2.Name<>'Boscop' and t1.ColorName<>'green'

Answered by akshaivk on February 20, 2021

How to exclude data in mySQL string?

2 Answers

Add your own answers!

Ask a Question