CODEWITHSUNDEEP
phpmysqlfilter
user1259798
user1259798

Reputation: 171

PHP Query To Filter Many-To-Many MySQL

I have the following query:

SELECT DISTINCT Names FROM all_uk
INNER JOIN cat_uk ON all_uk.Ref = cat_uk.Ref
INNER JOIN res_uk ON all_uk.Ref = res_uk.Ref
WHERE (cat_uk.Cat_Ref IN (25, 35)) AND (res_uk.Res_Ref = 1) AND (res_uk.Res_Ref = 2)

As you can see, the first part of the WHERE query is cumulative (everything that has either a 25 or a 35 ref). The second part is where I'm failing: I want to restrict the results to those where they have 1 AND a 2 in their second category.

Selecting one of these (i.e. either 1 OR 2) returns results, but when they're both selected I get nothing.

Can anyone help?

Upvotes: 0

Views: 35

Answers (1)

Mihai
Mihai

Reputation: 26784

I think you want this query

SELECT Names FROM all_uk
INNER JOIN cat_uk ON all_uk.Ref = cat_uk.Ref
INNER JOIN res_uk ON all_uk.Ref = res_uk.Ref
WHERE (cat_uk.Cat_Ref IN (25, 35)) 
GROUP BY Names
HAVING SUM(res_uk.Res_Ref = 1)>0
   AND SUM(res_uk.Res_Ref = 2)>0

Upvotes: 1

Related Questions