问题描述

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".

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:使用“In"；有多个子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！