CODEWITHSUNDEEP
mysql
T. Desmarez
T. Desmarez

Reputation: 23

Set a list in a variable in subquery - MYSQL

My problem is the following, I want set a list of ID in a variable, then use this variable in a subquery. The problem is that WorkBench (my GUI) return the following error : "subquery returning multiple rows". It seems to me that's what I want.
Please explain me where I am wrong.

This is my query :

set @listID := (select ID_VOIE as ID from voies
    where ORIGINE = 'XXX'
    group by CODE_INSEE, CODE_VOIE
    having count(*) > 1);

select substring(v.CODE_INSEE,1,2), count(*) from voies v
    where v.ID_VOIE in (@listID)
    group by substring(vs.CODE_INSEE,1,2);

The thing is I'm blocked with the "group by", I want do a groupd by after a first group by, that's why I can't (or at least i didn't find a way) write the request with a single WHERE clause.

The thing is I know that I can put the whole request directly in my subquery instead of using variable but :

So I seek 2 possible ways : a way that let me use a list in a variable in a subquery OR a way that let me use "group by" twice in a single query.

Thanks you in advance for your answers (oh and sorry for my english, this is not my maternal language).

Upvotes: 0

Views: 1377

Answers (1)

Steve Lovell
Steve Lovell

Reputation: 2574

Unless you need that variable for something else, you should be able to skip it entirely as follows:

SELECT
    SUBSTRING(v.CODE_INSEE,1,2),
    COUNT(*)
FROM
    voies v
WHERE
    v.ID_VOIE in
    (SELECT
         ID_VOIE as ID
     FROM
         voies
     WHERE
         ORIGINE = 'XXX'
     GROUP BY
         CODE_INSEE,
         CODE_VOIE
     HAVING COUNT(*) > 1)
GROUP BY
    SUBSTRING(vs.CODE_INSEE,1,2);

As you say, the subquery will be executed for all rows. To avoid that, a variable would be best, but MySQL doesn't support table variables. Instead, you can use a temporary table:

IF EXISTS DROP TABLE myTempTable;
CREATE TEMPORARY TABLE myTempTable (ID_VOIE int); -- I don't know the datatype
INSERT INTO myTempTable (ID_VOIE)
SELECT DISTINCT -- using distinct so I can join instead of use IN.
    ID_VOIE as ID from voies
WHERE
    ORIGINE = 'XXX'
GROUP BY
    CODE_INSEE, CODE_VOIE
HAVING COUNT(*) > 1

And now you can do this:

SELECT
    SUBSTRING(v.CODE_INSEE,1,2), COUNT(*)
FROM
   voies v
   JOIN myTempTable tt ON
       v.ID_VOIE = tt.ID_VOIE
GROUP BY SUBSTRING(vs.CODE_INSEE,1,2);

Upvotes: 1

Related Questions