CODEWITHSUNDEEP
sqloracle-databaseselect
Praveen Kumar
Praveen Kumar

Reputation: 815

Select statement for Oracle SQL

I have a table say, 

column1 column2
a   apple
a   ball
a   boy
b   apple
b   eagle
b   orange
c   bat
c   ball
c   cork

Now I would like to fetch column1 based on the rows that doesn't contain 'apple' and also ignore values in column1 if any of the rows have 'apple' in it. So in the table above only 'C' must be retured. I am kind of new to Oracle SQL and I know Select column1 from table where column2 != 'apple' will not work. I need some help with this please.

Upvotes: 0

Views: 137

Answers (4)

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

You could use DISTINCT with NOT IN in following:

QUERY 1 using NOT IN

select distinct col1
from t
where col1 not in (select col1 from t where col2 = 'Apple')

QUERY 2 using NOT EXISTS

As per @jarlh comment you could use NOT EXISTS in following:

select distinct col1
from #t t1
where not exists (select 1 from #t t2 where col2 = 'Apple' and t1.col1 = t2.col1)

SAMPLE DATA

create table t
(
    col1 nvarchar(60),
    col2 nvarchar(60)
)
insert into t values
('a','apple')
,('a','ball')
,('a','boy')
,('b','apple')
,('b','eagle')
,('b','orange')
,('c','bat')
,('c','ball')
,('c','cork')

Upvotes: 1

Aleksej
Aleksej

Reputation: 22949

If I understand well, you need the values af column1 such that in your table does not exist a row with the same value of column1 and 'apple' in column2; you can translate this in SQL with:

Select column1
from your_table t
where not exists ( 
                   select 1
                   from your_table t2
                   where t2.column1 = t1.column1
                     and t2.column2= 'apple'
                 )

This is only one of the possible ways to get your result, soyou can rewrite it in many ways; I believe this way of writing is similar enough to the logics to clearly explain how a logic could be written in plain SQL.

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35583

Select * from tbl
Left join (
    Select column1 from tbl
    Where column2 like '%apple%'
    Group by column1
    ) g on tbl.colum1 = g.column1
Where g.column1 is null

Seems to me that you need to find a summary of all colum1 values that have any reference to apple. Then list the rows that have no match to the summary list (g)

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

Assuming that column1 is NOT NULL you could use:

SELECT DISTINCT t.column1
FROM table_name t
WHERE t.column1 NOT IN (SELECT column1
                        FROM table_name
                        WHERE column2 = 'apple');

LiveDemo

To get all columns and rows change DISTINCT t.column1 to *.

Upvotes: 0

Related Questions