CODEWITHSUNDEEP
mysqlsql
Arkadiusz
Arkadiusz

Reputation: 1875

How to filter columns of one table by values given as rows in another table?

I have two tables in MySQL. I would like to select columns from main_table which are given as rows in cols_filter table (it is a small table which can be edited at any time).

main_table:

+--------+--------+---------+-------+-------+
| Col_A  | Col_B  |  Col_C  | Col_D | Col_E |
+--------+--------+---------+-------+-------+
| Apple  | 585416 | Monday  |   0   |   Y   |
| Banana | 857463 | Sunday  |   1   |   N   |
| Orange | 852147 | Friday  |   0   |   N   |
| Plum   | 753951 | Sunday  |   1   |   Y   |
| Peach  | 448691 | Monday  |   0   |   N   |
+--------+--------+---------+-------+-------+

cols_filter:

+--------+
| names  |
+--------+
| Col_A  |
| Col_B  |
| Col_E  |
+--------+

Expected output:

+--------+--------+-------+
| Col_A  | Col_B  | Col_E |
+--------+--------+-------+
| Apple  | 585416 |   Y   |
| Banana | 857463 |   N   |
| Orange | 852147 |   N   |
| Plum   | 753951 |   Y   |
| Peach  | 448691 |   N   |
+--------+--------+-------+

I was trying to create a subquery with GROUP_CONCAT to have all rows from cols_filter as comma seperated values in one line:

SELECT GROUP_CONCAT(names) AS Columns
FROM cols_filter

+---------------------+
|       Columns       |
+---------------------+
| Col_A, Col_B, Col_E |
+---------------------+

... and using it as a list in main query instead of putting certain column names there, but it did not work.

I was not able to find a similar question here. Any ideas?

Upvotes: 0

Views: 114

Answers (1)

Athi
Athi

Reputation: 391

Framing SQL cannot be dynamic within database itself, but can be achieved through little programming on top. You can use any programming language(python/java/scala) to generate the SQL dynamically and fire the query to the database.

Upvotes: 1

Related Questions