MySQL: Use subquery results in another subquery

Question

I need to perform the following MySQL query in a Wordpress installation:

Get the category IDs where the category name is "Oppskrifter".
Get the child categories of all these IDs.
Get the children of the resulting child categories (i.e. the children of 2. and the grandchildren of 1.).

I am trying to solve this using subqueries with aliases, but I am not able to make it work nor am I certain that this is the best way of doing it. Here is my current take:

SELECT * FROM
(
    SELECT term_id AS oppskrifter_id
    FROM wp_terms WHERE name = "Oppskrifter"
) AS R1,
(
    SELECT * FROM wp_terms AS t
    INNER JOIN wp_term_taxonomy AS tt
        ON tt.term_id = t.term_id
    WHERE tt.taxonomy = "category" AND tt.parent IN (R1)
) AS R2;

The first subquery works just fine by itself, but when introducing the second one it does not know what R1 is yet, thus I get the error ERROR 1054 (42S22): Unknown column 'R1' in 'having clause'. If I substitute R1 by the actual query, SELECT term_id AS oppskrifter_id FROM wp_terms WHERE name = "Oppskrifter" I get the error message ERROR 1060 (42S21): Duplicate column name 'term_id'.

What I was thinking to do here was to get the children (bullet point 2) in the second query and then do the exact same thing for the grandchildren using those results.

What are my options to solve this? What are the proper ways of doing it, both speed-wise and readability-wise?

R1 refers multiple columns yet you are trying to search tt.parent column in collection of columns. — px5x2
– px5x2, Commented Nov 5, 2015 at 7:54
It's not entirely clear what result you want to achieve. Is it rowset of both subqueries or only the last one? Fiddle or example with dummy data would be perfect — Wintermute
– Wintermute, Commented Nov 5, 2015 at 8:32
The ideal result is to get a rowset of both subqueries together, since I need to have a mapping from child to grandchild. That is, I need to know which grandchildren belong to which child of the "Oppskrifter" category. — Krøllebølle
– Krøllebølle, Commented Nov 5, 2015 at 8:47
As far as your current query goes, you can replace R1 with actual query and then use UNION to merge both. Something like this: sqlfiddle.com/#!9/c97b3/10 I doubt it's what you expect and it's suboptimal anyways. It's just I still struggle to understand expected result — Wintermute
– Wintermute, Commented Nov 5, 2015 at 9:09
I changed the data set in your fiddle and put it here (the query went away for some reason): sqlfiddle.com/#!9/100af I cannot get the SQL to run though, it just says something went wrong. The query I used is the exact same as you had, except I changed "Oppskrifter" to "Recipes". I hope the data set there is clearer as to what I want to achieve: I need to get "Dinner", "Lunch", "Dessert" together with their children categories ("Meat", "Chicken", "Vegetarian", etc.), but "Christmas" is to be left out. — Krøllebølle
– Krøllebølle, Commented Nov 5, 2015 at 9:40

Samir Selia · Accepted Answer · 2015-11-05 08:53:55Z

1

Use Join on all 3 tables.

Note: Replace wp_grand_child with actual table name and wg.term_id with actual field name

SELECT wt.term_id AS oppskrifter_id, wtt.term_id AS child_id, wg.term_id AS grand_child_id
FROM wp_terms AS wt
JOIN wp_term_taxonomy AS wtt ON wtt.term_id = wt.term_id
JOIN wp_grand_child AS wg ON wg.term_id = wtt.term_id
WHERE wt.name = "Oppskrifter"
AND wtt.taxonomy = "category"

answered Nov 5, 2015 at 8:53

Samir Selia

7,1502 gold badges14 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL: Use subquery results in another subquery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related