CODEWITHSUNDEEP
mysqlsubquery
Nicola
Nicola

Reputation: 91

MySQL: Select based on values of another table

Problem:

In MySQL, I have the following table:

table1:

id           date               Count_0    Count_1      Count_2
1     2018-03-15 09:29:01         1          3            5  
2     2018-03-15 09:29:11         2          4            6

In this table, a new row is entered every 10 seconds with Count data (tables are actually several thousands of rows long) and have a fixed number of columns Count_x (say from 0 to 9).

I have another table which has the following structure:

table2:

id       name             
Count_0   a  
Count_1       
Count_2   c

This table has as many rows as the first table has Count_x columns.

Is it possible to have a query which will contain the data from the first output but only considering the columns which have a name in the second table plus de date ? In this example, the output would be:

       date              Count_0    Count_2      
2018-03-15 09:29:01        1          5  
2018-03-15 09:29:11        2          6

Bonus question: Is it possible to automatically change column names according to data in second table ? in this example the output would be:

       date                a          c      
2018-03-15 09:29:01        1          5  
2018-03-15 09:29:11        2          6

My tests:

I've tried using pivots which didn't work, or combined SELECT statements didn't really work:

SELECT id FROM table2 WHERE name <> ''

gives me correctly the Count_x with existing names

SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='db' AND `TABLE_NAME`='table1';

gives me correctly the column names of table1. Now I don't know how to combine those 2 queries. Am I going into the right direction ?

Thank you for your advice.

Upvotes: 0

Views: 1391

Answers (1)

MarcinEl
MarcinEl

Reputation: 219

Try this:

SET @colname = null;
SET @query = null;
select group_concat(count_name) from table2 where name is not null into 
@colname;

SET @query = CONCAT('select date, ',@colname,' from table1');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Upvotes: 2

Related Questions