CODEWITHSUNDEEP
mysqldatabaserelational-database
user2550696
user2550696

Reputation: 111

MySQL : writing query for relational tables for strict categories

I want to write a query that will select me the questions with specific countries ONLY, so when I select questions containing country 1 and 2 show me ONLY questions that have 2 countries and those countries are 1 and 2.

I have 3 tables - questions, countries, questions_has_countries

I tried : 

SELECT q.question, q.id , c.id  
FROM questions as q, countries as c,  questions_has_countries as qhc
WHERE c.id = qhc.countries_id 
AND qhc.questions_id = q.id 
AND c.id IN (1,2) 
GROUP BY q.question
HAVING COUNT( qhc.countries_id ) = 2

When i use line 5 ( AND c.id IN (1,2) ) my counter stops working correctly cuz it's narrowing down the search. I know i have to rewrite it but i don't know how. Pls help.

Upvotes: 3

Views: 58

Answers (1)

John Woo
John Woo

Reputation: 263893

remove the WHERE clause and change the HAVING clause into

HAVING  SUM(c.id IN (1,2)) = 2 AND
        COUNT(DISTINCT c.id) = 2

Upvotes: 2

Related Questions