CODEWITHSUNDEEP
mysql
José Nobre
José Nobre

Reputation: 5027

How to check different condition for same row?

I have a row where I want to apply filter's to it. And I want to find for example every restaurant that has the category = 1 and 2. How can I apply this on My Sql query

This is my code (Only the were part) : 

SELECT * From establishment WHERE `subcategories`.`id` = 1 AND `subcategories`.`id` = 21;

And the query is returning me empty. How can I do to have result's?

Here are my tables

Establishment
id
name


SubCategories
id
name


EstablishmentSubCategories

sub_category_id
establishment_id

Upvotes: 0

Views: 218

Answers (1)

forpas
forpas

Reputation: 164089

This query:

SELECT establishment_id
FROM EstablishmentSubCategories
WHERE sub_category_id IN (1, 21)
GROUP BY establishment_id
HAVING COUNT(*) = 2

returns the establishment_ids that you want, so you can use it with the operator IN: 

SELECT * 
FROM establishment 
WHERE id IN (
  SELECT establishment_id
  FROM EstablishmentSubCategories
  WHERE sub_category_id IN (1, 21)
  GROUP BY establishment_id
  HAVING COUNT(*) = 2
)

Or with EXISTS:

SELECT e.* FROM establishment e
WHERE
  EXISTS(SELECT 1 FROM EstablishmentSubCategories c WHERE c.establishment_id = e.id AND sub_category_id = 1)
  AND 
  EXISTS(SELECT 1 FROM EstablishmentSubCategories c WHERE c.establishment_id = e.id AND sub_category_id = 21)

Upvotes: 2

Related Questions