Pass variable value inside nested SELECT

Asked

Viewed 2,147 times

6

Assuming the following query where we are selecting values, grouping them and then re-grouping the result:

SELECT
    CONCAT (b.label, '|', b.slug)
FROM (
    SELECT
        group_concat(name SEPARATOR '$') AS label,
        group_concat(slug SEPARATOR '$') AS slug
    FROM (
        SELECT
            color.name,
            color.slug
        FROM color
        INNER JOIN product__colors USING ( color_id )
        WHERE product__colors.product_id = 1 -- integer fixo funciona
    ) AS a
) AS b

Output example:

azul$vermelho$branco verde|azul$vermelho$branco-verde

But the query is to perform with the value of product__colors.product_id variable and not 1 as in the example above:

SELECT
    CONCAT (b.label, '|', b.slug)
FROM (
    SELECT
        group_concat(name SEPARATOR '$') AS label,
        group_concat(slug SEPARATOR '$') AS slug
    FROM (
        SELECT
            color.name,
            color.slug
        FROM color
        INNER JOIN product__colors USING ( color_id )
        WHERE product__colors.product_id = ? -- integer variável não funciona
    ) AS a
) AS b

Question

How can we pass a variable value to the innermost condition ?

mysql select group-concat
  • 1

    It was not very clear to me what exercise and what you want to move from query to subquery. I suspect that this query could be written differently to achieve the result you would like. But in advance, for this type of problem, where the subquery needs query information, it is usually used JOIN or WHERE. See: http://www.geeksengine.com/database/subquery/correlated-subquery.php

1 answer

1

It really wasn’t clear maybe but you say "don’t perform", I tested here and ran, maybe the way you’re declaring the variable is giving some problem, that part you didn’t demonstrate, follow my test.

set @variavel = 1;
select 
    CONCAT(b.label,b.slug)    
from (
    SELECT
        group_concat(func_nome SEPARATOR '$') AS label,
        group_concat(func_id SEPARATOR '$') AS slug
    FROM (
        SELECT
            f.func_nome,
            f.func_id
        FROM tab_funcionario f
        INNER JOIN tab_jornada_mot USING ( func_id )
        WHERE tab_jornada_mot.func_id = @variavel -- declarada no inicio do código
    ) AS a
) as b

I made some changes in table names/ attributes because I did the test in a BD that I had ready, but I believe it is clear.

Maybe you made the variable declaration otherwise ex:

declare variavel int;
select ...

This way I did not test, but as above works.

Browser other questions tagged mysql select group-concat

You are not signed in. Login or sign up in order to post.