SQL Simple Query - Multiple entries filtering

Question

Newbie SQL Query question: I'm using Oracle SQL Developer.

I have a table:

<name>  <color>
steve    red
mark     red
steve    green
john     red
ryan     red

What I would like to do is have the results only display the names that contain RED in the color column.
The results would be:

     mark
     john
     ryan

Since steve has a color green as well, it would not show up.

Anyone out there knows?

EDIT:
THANK YOU FOR THE ANSWERS. I do see that I should have said "red and no other color" in the results. Sorry for the confusion but thank you for the super speedy replies!

What did you try ? This would in the first pages of any book/tutorial/lesson on SQL for beginners. — ApplePie
– ApplePie, Commented Aug 27, 2012 at 21:53
Not necessarily - I don't think this can be solved without a subquery or a join. — james
– james, Commented Aug 27, 2012 at 21:55
How can you say this without knowing anything of his database ? For all we know both fields are in the same table which makes it a simple SELECT ... WHERE query. — ApplePie
– ApplePie, Commented Aug 27, 2012 at 22:01

Taryn · Accepted Answer · 2012-08-27 21:58:45Z

2

You will just query with a WHERE clause and a NOT IN.

SELECT name
FROM yourTable
WHERE color = 'red'
   AND name NOT IN (
                    SELECT name
                    FROM yourTable
                    WHERE color <> 'red'
                )

see SQL Fiddle with Demo

Or you can use WHERE with NOT EXISTS:

SELECT name
FROM yourTable t1
WHERE color = 'red'
   AND NOT EXISTS (
                    SELECT  t2.name
                    FROM yourTable t2
                    WHERE  t2.color <> 'red'
                     AND t1.name = t2.name
                )

see SQL Fiddle with Demo

edited Aug 27, 2012 at 21:58

answered Aug 27, 2012 at 21:53

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2012-08-27 22:16:01Z

2

Just for the fun of it, here is a solution using windowing functions:

select *
from (
  select name, 
         color, 
         count(distinct color) over (partition by name) as color_cnt
  from the_table
) t
where color = 'red' 
and color_cnt = 1;

SQLFiddle: http://sqlfiddle.com/#!4/d267e/3

There is a slight change that this might be faster than the solutions with a sub-select because most probably only a single scan over the table will be needed (although possibly the other solutions could use an index on the color column which could make the two scans cheaper than the single scan)

answered Aug 27, 2012 at 22:16

user330315

1 Comment

EPRINGLES Over a year ago

Thank you! I appreciate the different POV :)

Randy · Accepted Answer · 2012-08-27 21:53:57Z

1

SELECT name
FROM yourTable
WHERE color = 'red'
and name not in (
SELECT name
FROM yourTable
WHERE color != 'red')

answered Aug 27, 2012 at 21:53

Randy

16.7k2 gold badges40 silver badges56 bronze badges

Comments

james · Accepted Answer · 2012-08-27 21:54:03Z

1

SELECT DISTINCT name
FROM mytable
WHERE color = 'red'
AND name NOT IN (
    SELECT DISTINCT name
    FROM mytable
    WHERE color <> 'red'
)

answered Aug 27, 2012 at 21:54

james

13.4k9 gold badges50 silver badges74 bronze badges

Comments

codingbiz · Accepted Answer · 2012-08-27 22:25:32Z

1

This should do it Everyone has a fiddle for you

select t.name from t
join 
   (select name,count(1) cnt from t
     group by name
      having count(1) = 1) n
      on t.name = n.name

edited Aug 27, 2012 at 22:25

answered Aug 27, 2012 at 21:56

codingbiz

26.4k9 gold badges62 silver badges99 bronze badges

1 Comment

james Over a year ago

Doesn't work (returns steve which is not desired). sqlfiddle.com/#!3/d699a/3

DarkAjax · Accepted Answer · 2012-08-27 22:04:13Z

0

I guess this is what you want:

SELECT DISTINCT <name> 
FROM <table> 
WHERE <color> = 'red'
AND <name> NOT IN (SELECT DISTINCT <name> 
                     FROM <table> 
                     WHERE <color> != 'red')

edited Aug 27, 2012 at 22:04

answered Aug 27, 2012 at 21:53

DarkAjax

16.3k11 gold badges60 silver badges67 bronze badges

4 Comments

EPRINGLES Over a year ago

This will return Steve, which is a result I don't want.

james Over a year ago

Since steve has a color green as well, it would not show up. Not worded extremely well, but it's there...

DarkAjax Over a year ago

The bad wording made me think the OP had a non-working query and meant something else with that phrase, but I gues you're right...

EPRINGLES Over a year ago

Sorry for the confusion, but thanks for the reply. I didn't downvote, btw. I don't even think I can.

Collectives™ on Stack Overflow

SQL Simple Query - Multiple entries filtering

6 Answers 6

see SQL Fiddle with Demo

see SQL Fiddle with Demo

Comments

1 Comment

Comments

Comments

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

see SQL Fiddle with Demo

see SQL Fiddle with Demo

Comments

1 Comment

Comments

Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related