MySQL: Select based on values of another table

Question

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.

Id suggest you revise the design of your schema

Strawberry
– Strawberry

2018-03-27 12:45:09 +00:00
Commented Mar 27, 2018 at 12:45 — Strawberry
– Strawberry, Commented Mar 27, 2018 at 12:45

MarcinEl · Accepted Answer · 2018-03-27 14:28:02Z

2

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;

answered Mar 27, 2018 at 14:28

MarcinEl

2192 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nicola Over a year ago

Thank you for your answer, it works correctly in a procedure. However, is it possible to have the same in a query ?

MarcinEl Over a year ago

There is no possibility to do the same in a query. For example in normal select You have constant set of selected rows.

Collectives™ on Stack Overflow

MySQL: Select based on values of another table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related