MySQL Query for Multiple Columns to be FILTERED?

Question

Lets say i have 1 Table only with multiple columns. Lets say:

Fruits
--------------------------------------
id  | name        | country
--------------------------------------
1   | Banana      | china
2   | Orange      | japan
3   | Apple       | japan
4   | Apple       | china
5   | Banana      | usa
6   | Orange      | china

Then the simple query to SELECT all Fruits where id < 6.
It is:

SELECT * FROM fruits WHERE id < 6

Then it will return:

--------------------------------------
id  | name        | country
--------------------------------------
1   | Banana      | china
2   | Orange      | japan
3   | Apple       | japan
4   | Apple       | china
5   | Banana      | usa

Then by appending above query, how can i go on to EXCLUDE something more.
For example:

Do NOT want to see, any "APPLE" from "CHINA".

So the final result must be:

--------------------------------------
id  | name        | country
--------------------------------------
1   | Banana      | china
2   | Orange      | japan
3   | Apple       | japan
5   | Banana      | usa

This is again without the row that having if, name='Apple' AND country='china' TOGETHER!.

So by continuing the first query, how can i ADD the Additional "AND Condition Pair" to get this result, please?

I would say:

SELECT * FROM fruits WHERE id < 6 ... AND DON'T RETURN this condition where name='Apple' and country='china' TOGETHER

CloudyMarble · Accepted Answer · 2013-04-22 11:33:43Z

2

Try:

SELECT * 
FROM fruits 
WHERE (id < 6)
AND NOT (name = 'Apple' AND country = 'china')

Or:

SELECT * 
FROM fruits 
WHERE (id < 6)
AND id NOT IN (SELECT id 
               FROM fruits
               WHERE name = 'Apple'
               AND country = 'china')

edited Apr 22, 2013 at 11:33

answered Apr 22, 2013 at 11:02

CloudyMarble

37.7k70 gold badges103 silver badges133 bronze badges

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

Comments

echo_Me · Accepted Answer · 2013-04-22 11:13:57Z

1

try this

 SELECT * FROM fruits WHERE 
                 (name ,country) not in ( select 'Apple' ,'china' from Fruits)

 HAVING id < 6

DEMO HERE

edited Apr 22, 2013 at 11:13

answered Apr 22, 2013 at 11:02

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Collectives™ on Stack Overflow

MySQL Query for Multiple Columns to be FILTERED?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related