Dynamic GROUP CONCAT with SUBQUERY

Question

I think it's not a difficult problem but I don't find any subject to solve it.

1 'DRIVER' drive 1 'TRUCK' at a time, 1 'TRUCK' carry multiple 'BOXS' at a time.

I have two queries :

The simple one :

Select a.*,
   (Select IDTRUCK From TRUCKS WHERE ACTUEL= true AND IDDRIVER=IDDRIVER) as IDTRUCK
From DRIVERS a;

result :

  IDDRIVER | NAMEDRIVER | IDTRUCK
  -------------------------------
     1     |   Michel   |    45
     2     |    Jean    |    35

and the complicated one (Took from here) :

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT
  CONCAT(
    'MAX(IF(`IDBOX` = ', `IDBOX`, ',WEIGHTBOX,NULL)) AS WEIGHTBOX', `IDBOX`)
  ) INTO @sql

FROM BOXS;


SET @sql = CONCAT('SELECT  IDTRUCK, ', @sql, ' 
              FROM    BOXS WHERE IDTRUCK =
                    (Select IDTRUCK From TRUCKS WHERE ACTUEL= true AND IDDRIVER=IDDRIVER)
              GROUP   BY IDTRUCK');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

result :

IDTRUCK | WEIGHTBOX1 | WEIGHTBOX2
-------------------------------
   45   |   75.2     |    46.3
   35   |   154      |    69.4

I want this two results in one table :

IDDRIVER | NAMEDRIVER | IDTRUCK | WEIGHTBOX1 | WEIGHTBOX2
---------------------------------------------------------
   1     |   Michel   |   45    |   75.2     |    46.3
   2     |    Jean    |   35    |   154      |    69.4

But I don't know how to make those two queries go together.

I think a JOIN can do the work but I don't manage to succeed.

PS: Sorry for my very basic English.

EDIT: According to Shadow, it's possible to do this with a left join (here) but I don't know where do I have to put the differents parts of my queries to make them work. It's not a problem of understanding, it's a problem of syntax.

It may be the good solution but I don't know how to write it with my example. Thanks anyway. — Barrelsrider, May 23 '16 at 11:01
Have you **tried** the proposed solution? If yes, what have you tried and what has gone wrong? — Shadow, May 23 '16 at 11:16
I don't know where do I have to put the join. In my first select, in my second one, in my group concat, etc... but @Andrew find a solution that might work. — Barrelsrider, May 23 '16 at 12:05

score 0 · Accepted Answer · answered May 23 '16 at 11:18

0

You can include additional tables into your dynamic query. Something like this:

SET @sql = CONCAT('
  SELECT d.*, t.IDTRUCK, ', @sql, ' 
  FROM DRIVERS d
    JOIN TRUCKS t ON ACTUEL = TRUE AND t.IDDRIVER = d.IDDRIVER;
    LEFT JOIN BOXS b ON BOXS.IDTRUCK = t.IDTRUCK
  GROUP BY t.IDTRUCK');

answered May 23 '16 at 11:18

Andrew

1,858
13
15

Thanks it work almost perfectly but I have as much column as Box, in all column, there is one value and all the rest is 'null'. How do I remove the 'null' and have the smallest column possible ? – Barrelsrider May 23 '16 at 12:01

Dynamic GROUP CONCAT with SUBQUERY

1 Answers1