how to filter a table with where-CodePudding

I'm trying to filter my table 'mange' with the condition of customers who eat both the "mexicaine" and the "végétarienne".

I have a table 'mange' which contains 'nom' and 'pizza'

I have tried to filter with

select *
from mange
where ( pizza = 'végétarienne')
and ( pizza = 'mexicaine')

But I don't get anything even though I have some 'nom' who eat both.

CodePudding user response：

SELECT nom, count(distinct pizza) as DistinctpizzaCNt
FROM mange
WHERE ( pizza in 'végétarienne', 'mexicaine')
GROUP BY nom
having count(distinct pizza) = 2

or use set based logic

SELECT nom
FROM mange
WHERE ( pizza = 'végétarienne')

INTERSECT

SELECT nom
from Mannge
WHERE ( pizza = 'mexicaine')

CodePudding user response：

A WHERE clause is a Boolean expression that each row is tested against. Only if a row returns TRUE for the whole expression does the row get returned.

So, a 'végétarienne' row would be tested by your WHERE clause and yield TRUE AND FALSE, which according to Boolean logic yields FALSE.

Similarly, a 'mexicaine' row would be tested by your WHERE clause and yield FALSE AND TRUE, which according to Boolean logic also yields FALSE.

Note: Because no individual row is ever both 'végétarienne' AND 'mexicaine', no row will ever be returned by your WHERE clause.

You could either use OR, or use IN(). I'd recommend the latter...

select *
  from mange
 where ( pizza = 'végétarienne')
    or ( pizza = 'mexicaine')

Or...

select *
  from mange
 where pizza IN ('végétarienne', 'mexicaine')

CodePudding user response：

The reason why you don't get anything is because you're trying to evaluate pizza = 'végétarienne' and pizza = 'mexicaine' at the same time. When a pizza is both vegetarian and mexicane? Never.

That's why you would require the OR condition here, to make sure either one of them is retrieved:

SELECT * 
FROM mange 
WHERE (pizza = 'végétarienne') OR (pizza = 'mexicaine')

In this case you'd retrieve people that eat either one of them, or both. If you want to restrict it to the people that like both and not only one, then you can look for people that like vegetarian, people that like mexicaine and intersect the two sets using the INTERSECT operation as follows:

SELECT * FROM mange WHERE pizza = 'végétarienne'
INTERSECT 
SELECT * FROM mange WHERE pizza = 'mexicaine'

CodePudding user response：

Your goal is not 100% clear to me. My first impression was you are just looking for an IN clause to fetch both pizzas, 'végétarienne' and 'mexicaine':

SELECT *
FROM mange
WHERE pizza IN ('végétarienne','mexicaine');

An IN clause will fetch all entries having one of its values. Since a pizza can't be "végétarienne" and "mexicaine" the same time, we can't build this up using AND, but need an IN clause or OR.

But at second glance, paying attention to your requirement to show the customers who eat both the "mexicaine" and the "végétarienne", this would be an incorrect approach because it would also list such customers who eat only one of them.

So if you are only looking for those customers who eat both pizzas, this can be done this way, using two IN clauses:

SELECT *
FROM mange
WHERE nom
IN
(SELECT nom
FROM mange
WHERE pizza = 'végétarienne')
AND nom 
IN 
(SELECT nom
FROM mange
WHERE pizza = 'mexicaine');

CodePudding user response：

jonas metlezer , citron, MatBailie my question was not clear because my English is not good but thank you all. The double clause IN AND IN I was able to solve the problem. Thank you very much.