CODEWITHSUNDEEP
mysqlsql
Tim
Tim

Reputation: 424

Join new columns based on row values

I have a table called flux with :

INSERT INTO `flux` (`id`, `name`) VALUES

(20, 'test1'),
(21, 'test2');

And a table called flux_rules with :

INSERT INTO `flux_rules` (`id`, `id_flux`, `id_modele_scrape`, `disallow`) VALUES

(1, 20, 1, 1),
(2, 21, 1, 1);

And one more table called modele_scrape with :

INSERT INTO `modele_scrape` (`id`, `modele`) VALUES

(1, 'renault captur'),
(2, 'renault zoe');

I believe this is a pretty usual setup, but what I haven't found information on what I'm trying to achieve, which is a SELECT query that would list all rows from modele_scrape with a column for each row in flux where the value is defined by the flux_rules column (1 IF EXISTS, ELSE 0) :

modele_scrape.id     modele_scrape.modele    test1    test2
1                    test                    1        0

Right now I have working query, but it only works to join one row from flux :

SELECT id AS id, 

(SELECT IF (EXISTS (SELECT id_flux, id_modele_scrape FROM flux_rules WHERE id_flux = 20 AND id_modele_scrape = modele_scrape.id), 1, 0) ) AS disallowed

FROM modele_scrape

The solution should work as to dynamically join X amount of columns where X is the amount of rows in flux (rows from this table can be added/deleted)

Upvotes: 0

Views: 201

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35593

Generically this sounds like a desire to "pivot" your information, and this may achieved by using "conditional aggregates" (basically this means using a case expression inside an aggregate function, typically this is the max() function.

select
      m.id 
    , m.modele
    , max(case when r.r.flux_id = 1 then flux.name end) as `name1`
    , max(case when r.r.flux_id = 2 then flux.name end) as `name2`
    , max(case when r.r.flux_id = 3 then flux.name end) as `name3`
from modele_scrape as m
left join flux_rules as r on m.id = r.id_modele_scrape
left join flux as f on r.flux_id  = f.id
group by
      m.id 
    , m.modele

Dynamic Pivot in MySQL

To produce such a pivot dynamically, the following query will work:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      ' max(case when  r.id_flux = ''',
      id,
      ''' then 1 else 0 end) ',
      name
    )
  ) INTO @sql
FROM
  flux;
  
set @sql = CONCAT('SELECT m.id, m.modele,', @sql,  
' from modele_scrape as m',
' left join flux_rules as r on m.id = r.id_modele_scrape',
' left join flux as f on r.id_flux  = f.id',
' group by m.id, m.modele') 
;

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

 id | modele         | test1 | test2
 -: | :------------- | ----: | ----:
  1 | renault captur |     1 |     1
  2 | renault zoe    |     0 |     0

see this as a working model here

NOTE: due to limited sample data I chose to produce the dynamic columns by directly querying the flux table, in the real tables this might produce more columns than you want/need so adjust the group_concat query, perhaps with joins to other tables and/or a where clause, to suit your data and requirements

Upvotes: 2

Related Questions