Question 90557 in PostgreSQL Question posted in
The official documentation can be found here.

how to filter a table with where – Postgresql

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.

Tags:

4

Answers


  1. 0

    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'
    Login or Signup to reply.
  2. 0

    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');
    Login or Signup to reply.
  3. 0

    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')
    Login or Signup to reply.
  4. 0 
    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')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search