How to combine these 2 SQL queries into one

Question

I have a 2-step query that I am trying to combine into one.

This returns the ids:

select  id
from    (select * from comments
        where deleted_at is NULL AND is_removed=0 and commentable_type LIKE "App%Comment"
         order by commentable_id, id) products_sorted,
        (select @pv := '26') initialisation
where   find_in_set(commentable_id, @pv)
and     length(@pv := concat(@pv, ',', id))
;

-- Then I take the results for parent id=26 (from prior query) and put them in an IN clause.

SELECT * FROM reactions 
WHERE deleted_at is NULL AND is_removed=0 AND reactable_type LIKE "App%Comment"
AND
reactable_id IN
(
30,
31,
33,
34,
50,
51,
52,
53,
36,
37,
38,
39,
40,
41,
42,
43,
44,
45,
5819,
6083,
5921,
6390,
54,
56,
57,
58,
59,
60,
61,
62,
5779
)
;

However when I combine the above 2 into one query, this does NOT work and returns a much shorter set of results:

----------------------
SELECT * FROM reactions r
WHERE r.deleted_at is NULL AND r.is_removed=0 AND r.reactable_type LIKE "App%Comment"
AND
r.reactable_id IN
(
select  id
from    (select * from comments
        where deleted_at is NULL AND is_removed=0 and commentable_type LIKE "App%Comment"
         order by commentable_id, id) products_sorted,
        (select @pv := '26') initialisation
where   find_in_set(commentable_id, @pv)
and     length(@pv := concat(@pv, ',', id))
)
;

What am I doing wrong?

Do you have some sample data that demonstrates the problem? – Nick Oct 01 '19 at 01:09 — Nick, Oct 01 '19 at 01:09
Share table structure and sample data. – James Oct 01 '19 at 04:26 — James, Oct 01 '19 at 04:26

score 1 · Answer 1 · answered Oct 01 '19 at 01:14

1

Depending on your version of mysql, you can use WITH

WITH
    first_query AS
    (

        select  id
        from    (select * from comments
                where deleted_at is NULL AND is_removed=0 and commentable_type LIKE "App%Comment"
                 order by commentable_id, id) products_sorted,
                (select @pv := '26') initialisation
        where   find_in_set(commentable_id, @pv)
        and     length(@pv := concat(@pv, ',', id))

    )

    SELECT
        *

    FROM
        reactions r

    WHERE
        r.deleted_at is NULL AND r.is_removed=0 AND r.reactable_type LIKE "App%Comment"
        AND
        r.reactable_id IN (SELECT DISTINCT * FROM first_query)

answered Oct 01 '19 at 01:14

artemis

6,857
11
46
99

1

Works when version is MySQL is 8+ – James Oct 01 '19 at 04:21
OP didn't specify what version they were using @James – artemis Oct 01 '19 at 11:51
Yeah, that's why i have added hint so that others or even OP notices, or just edit your answer by mentioning the version . – James Oct 01 '19 at 11:53
Gotcha. If OP or another user clicks on the hyperlink I embedded, they can also see it is for MySQL 8+ with example. – artemis Oct 01 '19 at 11:57
@James this is great. Is there a way to do this in MySQL v 5.8? – kp123 Oct 01 '19 at 18:52
I am also trying to solve for this related problem so your thoughts would be much appreciated! https://dba.stackexchange.com/questions/249950/mysql-recursive-query-that-returns-all-children-recursively-including-the-row-f?noredirect=1#comment492741_249950 – kp123 Oct 01 '19 at 18:52
No, you cannot do this in `v5.8` as evidenced by the post I linked, but your post on https://dba.stackexchange.com/questions/249950/mysql-recursive-query-that-returns-all-children-recursively-including-the-row-f?noredirect=1#comment492741_249950 says you can use `8.0`, so I suggest doing so. This should then answer your question. – artemis Oct 01 '19 at 19:09

How to combine these 2 SQL queries into one

1 Answers1