I have a table Studios where each studio can have a parentStudio.
+----+------+--------------+----------+
| id | name | parentStudio | criteria |
+----+------+--------------+----------+
| 0 | A | 6 | 0 |
+----+------+--------------+----------+
| 1 | B | 0 | 1 |
+----+------+--------------+----------+
| 2 | C | null | 0 |
+----+------+--------------+----------+
| 3 | D | 2 | 1 |
+----+------+--------------+----------+
| 4 | E | null | 1 |
+----+------+--------------+----------+
| 5 | F | null | 0 |
+----+------+--------------+----------+
| 6 | G | null | 0 |
+----+------+--------------+----------+
I need to query for studios matching some criteria but also I need to get all parents of all found studios. I got a query for getting the studios that match the criteria and I also got a query for getting all parents for a known studio. I don't know how to combine them into a single query.
The query I use to get all studios that match the criteria basically looks like this:
SELECT id FROM Studios WHERE criteria = 1
+----+
| id |
+----+
| 1 |
+----+
| 3 |
+----+
| 4 |
+----+
The query I use for getting all parents is:
SELECT
@currentId AS _id,
(
SELECT
name
FROM Studios
WHERE id = _id
) AS name,
(
SELECT
@currentId := parentStudio
FROM Studios
WHERE id = _id
) AS parentStudio,
@level := @level + 1 AS level
FROM
(
SELECT
@currentId := 1, --starting point
@level := 0
) AS vars,
Studios AS s
WHERE
@currentId IS NOT NULL
+----+------+--------------+-------+
| id | name | parentStudio | level |
+----+------+--------------+-------+
| 1 | B | 0 | 0 |
+----+------+--------------+-------+
| 0 | A | 6 | 1 |
+----+------+--------------+-------+
| 6 | G | null | 2 |
+----+------+--------------+-------+
The result I'm looking for would naively look like this:
SELECT DISTINCT
_id AS id,
name,
parentStudio,
level
FROM
(SELECT
@currentId AS _id,
(
SELECT
name
FROM Studios
WHERE id = _id
) AS name,
(
SELECT
@currentId := parentStudio
FROM Studios
WHERE id = _id
) AS parentStudio,
@level := @level + 1 AS level
FROM
(
SELECT
@currentId := (SELECT id FROM Studios WHERE criteria = 1),
@level := 0
) AS vars,
Studios AS s
WHERE
@currentId IS NOT NULL
)
But obviously this won't work because (SELECT id FROM Studios WHERE criteria = 1) provides more than one result.
The wanted result set would look like this:
+----+------+--------------+-------+
| id | name | parentStudio | level |
+----+------+--------------+-------+
| 1 | B | 0 | 0 |
+----+------+--------------+-------+
| 0 | A | null | 1 |
+----+------+--------------+-------+
| 6 | G | null | 2 |
+----+------+--------------+-------+
| 3 | D | 2 | 0 |
+----+------+--------------+-------+
| 2 | C | null | 1 |
+----+------+--------------+-------+
| 4 | E | null | 0 |
+----+------+--------------+-------+
Notice that id 5 (name F) is not in the result set. It doesn't match the criteria itself and it isn't an ancestor of any studio that does match the criteria.
Is there any way this could be done in only SQL (MySQL 5)?
