Pass variable value into nested SELECT

Question

Pass variable value into nested SELECT

Navigation

#1 by (1 votes)

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

asked by anonymous 09.04.2015 / 18:31

1 answer

Panel open closed JS How to copy a repeater on the same page

score 1 · Answer 1

It really was not clear maybe but you say "do not run", I tested here and executed, maybe the form that is declaring the variable is giving some problem, that part you did not 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 to the table / attribute names because I did the test on a DB that I had ready, but I think it's clear.

Maybe you made the declaration of the variable otherwise ex:

declare variavel int;
select ...

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