TransWikia.com

How to exclude data in mySQL string?

Stack Overflow Asked by Markus on February 20, 2021

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!

2 Answers

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'
                 );

Answered by Gordon Linoff on February 20, 2021

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

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP