Databases
StackOverflow2013
Postsdbo
POMySQL: Using "In" with Multiple SubQueries?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL: Using "In" with Multiple SubQueries?
    primarykey
    data
    text
    <p>I'm trying to use this query:</p> <pre> 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 </pre> <p>Which produces the error:</p> <pre><code>Sub-query returns more than 1 row </code></pre> <p>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.</p> <p>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:</p> <pre><code>WHERE P.PageID IN (2,4) </code></pre> <p>because 2 and 4 are the values the two sub-queries have in common.</p> <p>Is this possible with one query?</p> <p>UPDATE:</p> <p>I'm going to try to clarify the question a bit better.</p> <p>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.</p> <p>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.</p> <p>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.</p> <p><strong>UPDATE with SAMPLE DATA</strong></p> <p>Pages_Filters:</p> <pre> PageID FilterID FilterOptionID 297 2 5 297 7 57 297 9 141 305 2 5 101 2 5 101 7 57 </pre> <p>Pages:</p> <pre> PageID PageName 297 Some Page 305 Another Page 101 A Stupid Page </pre> <p>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"?</p> <p>The answer should be "only 297".</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNick
    1. USNick
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL: Using "In" with Multiple SubQueries?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload