MySQL: Using "In" with Multiple SubQueries?

Question

I'm trying to use this query:

SELECT COUNT(PF.PageID) AS Total,P.PageID
FROM Pages P
LEFT JOIN Pages_Filters PF ON PF.PageID=P.PageID
WHERE P.PageID IN (
    (SELECT PageID
       FROM Pages_Filters
       WHERE FilterID="1"
       AND FilterOptionID="2"
    ),
    (SELECT PageID
       FROM Pages_Filters
       WHERE FilterID="7"
       AND FilterOptionID="57"
    )
)
AND P.PageID !="283"
GROUP BY PF.PageID

Which produces the error:

Sub-query returns more than 1 row

I'd like for MySQL to merge the results of all the sub-queries, and use values in common as the IN clause in the main query.

So if the first sub-query returns 1,2,4 and the second sub-query returns 2,3,4, then the "in" clause would read:

WHERE P.PageID IN (2,4)

because 2 and 4 are the values the two sub-queries have in common.

Is this possible with one query?

UPDATE:

I'm going to try to clarify the question a bit better.

The result should return all of the pages which have the specified filters set to a specific value INCLUDING pages that have extra filters set which are not part of the query. In other words, I'm on a page with certain filters in place, show me all the pages that I can go to from here that have these same filters, and have one additional filter set.

It's a bit tricky to explain, but I'm looking for a result set that INCLUDES pages with other filters set, but NOT pages with a different value for the same filter ID.

So in the example, we want all pages that have filter 1 set to value 2, AND filter 7 set to 57. We also want pages with other filters (besides 1 and 7) assigned, but NOT pages who have filter 7 set to a value other than 57, and NOT pages who have filter 1 set to a value other than 2.

UPDATE with SAMPLE DATA

Pages_Filters:

PageID  FilterID    FilterOptionID
297        2           5
297        7           57
297        9           141
305        2           5
101        2           5
101        7           57

Pages:

PageID   PageName
297        Some Page
305        Another Page
101        A Stupid Page

In this case I should be able to ask, "What pages have Filter 2 set to 5, also have filter 7 set to 57, and also have filter 9 set to 141"?

The answer should be "only 297".

John Hartsock · Answer 1 · 2010-11-22T04:55:58.430

1

SELECT COUNT(PF.PageID) AS Total,P.PageID
FROM Pages P
LEFT JOIN Pages_Filters PF ON PF.PageID=P.PageID
WHERE (PF.FilterID="1"
       AND PF.FilterOptionID="2")
    OR (PF.FilterID="7"
       AND PF.FilterOptionID="57" )
AND P.PageID !="283"
GROUP BY PF.PageID

After thinking and reading Insane's comments I realized that this is maybe what you want. Basically the Derived Table I have created creates the intersection of your two queries above.

SELECT COUNT(PF.PageID) AS Total,P.PageID
FROM Pages P
LEFT JOIN (Select PF1.* from Pages_Filters PF1 
           INNER JOIN Page_Filters PF2 ON PF1.PageID = PF2.Page_ID
           WHERE PF1.FilterID = "1" AND PF2.FilterOptionID = "2"
             AND PF2.FilterID = "7" AND PF2.FilterOptionID = "57"
) PF ON PF.PageID=P.PageID
WHERE P.PageID !="283"
GROUP BY PF.PageID

edited Nov 22 '10 at 04:55

answered Nov 22 '10 at 04:12

John Hartsock

85,422
23
131
146

@John - i was thinking on the same lines but dont you think that this will not meet the OP's criteria where he wants only the Intersection of the results of the 2 OR clauses - but OR will give all values satisfying either condition. Specifically `So if the first sub-query returns 1,2,4 and the second sub-query returns 2,3,4, then the "in" clause would read: WHERE P.PageID IN (2,4)` is what i am referring to – Jagmag Nov 22 '10 at 04:19
@Insane...I understand that but this would be more efficient. – John Hartsock Nov 22 '10 at 04:46
@Nick .. Not sure if I'm on track with what you want but let me know. – John Hartsock Nov 22 '10 at 04:57
Please see my update above. This answer and the UNION ALL answer seem to return too many results. – Nick Nov 22 '10 at 05:06
@Nick ...Give some sample data because your question and the update do not make sense to me – John Hartsock Nov 22 '10 at 05:10
Your second one almost sort of works- But it always returns one extra result that's completely off the wall. The rest of the results it returns are correct. I'll try to come up with some example data. – Nick Nov 22 '10 at 08:37
I've added some sample data now, hopefully that helps. – Nick Dec 02 '10 at 23:49

score 1 · Answer 2 · answered Nov 22 '10 at 04:19

1

You should be able to "merge" the result sets union UNION/UNION ALL

Something like

SELECT COUNT(PF.PageID) AS Total,P.PageID
FROM Pages P
LEFT JOIN Pages_Filters PF ON PF.PageID=P.PageID
WHERE P.PageID IN (
    SELECT PageID
       FROM Pages_Filters
       WHERE FilterID="1"
       AND FilterOptionID="2"
    UNION ALL
    SELECT PageID
       FROM Pages_Filters
       WHERE FilterID="7"
       AND FilterOptionID="57"
)
AND P.PageID !="283"
GROUP BY PF.PageID

answered Nov 22 '10 at 04:19

Adriaan Stander

162,879
31
289
284

In this case just `UNION` is more efficient than `UNION ALL` since the `IN` clause does not benefit from duplicates brought by `UNION ALL`. – kintsukuroi Apr 26 '20 at 00:47

score 1 · Answer 3 · answered Nov 22 '10 at 05:36

1

Try This

SELECT COUNT(PF.PageID) AS Total,P.PageID
FROM Pages P
LEFT JOIN Pages_Filters PF ON PF.PageID=P.PageID
WHERE P.PageID IN (
    SELECT GROUP_CONCAT(DISTINCT(PageID))
    FROM Pages_Filters
    WHERE (FilterID="1" AND FilterOptionID="2")
    AND (FilterID="7" AND FilterOptionID="57")
)
AND P.PageID !="283"
GROUP BY PF.PageID

Or you can try something like

http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

from this link

answered Nov 22 '10 at 05:36

Naresh

785
1
11
23

The query doesn't seem to do what I'm looking for... But maybe there's some way to do it with EXISTS like you suggest? It might be possible to use EXISTS with UNION ALL and get what I want, but I'll have to play with it more. – Nick Nov 22 '10 at 08:49
Best of luck. If you get the way please post it. :) – Naresh Nov 22 '10 at 09:39

score 1 · Answer 4 · answered Nov 22 '10 at 18:55

Isolate the query that produces your list of PageIDs. You need to end up with a single query that returns the values you need. You can do that like this:

SELECT PageID P1 FROM Page_Filters
WHERE  FilterID = "1" AND FilterOptionID = "2" AND EXISTS
    (SELECT * FROM PageID P2 
    WHERE P2.PageID = P1.PageID AND FilterID = "7" AND FilterOptionID = "57")

MySQL: Using "In" with Multiple SubQueries?

4 Answers4

Linked