CODEWITHSUNDEEP
mysql
aimee
aimee

Reputation: 71

MySQL Conditionally Inner Join X # of columns

I have two tables, Table1 and Table2.

Based on a value inside a column on Table1, can I inner join a certain number of columns from Table2, JOIN on ID.

Table1:

id | col_number | 
1  |     2
2  |     3

Table2:

id | col1 | col2 | col3
1  | BRK | GOOG | APPL
2  |AMZN | INTC | TSLA

Expected Outcome, If the query was run for ID1:

id | col_number | col1  | col2
1  |   2        | BRK   | GOOG

I haven’t been able to find many examples of conditional inner joins easy enough for me to attempt to understand them. Those I have found are conditional on different tables, not columns.

Fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4efaf735c1f28fa8a9e55d77ca30fa71

Upvotes: 1

Views: 64

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562398

The select-list of an SQL query must be fixed before the query is parsed and prepared, and that happens before the query begins reading any rows of data. This means you can't make a query that returns a different number of columns depending on the data values in some of the rows it reads.

Also, any query result must have the same number of columns in every row, not a dynamic number of columns.

You could, however, make some of the expressions return NULL in some columns depending on a data value.

SELECT table1.id, table1.col_number,
  CASE WHEN table1.col_number >= 1 THEN table2.col1 ELSE NULL END AS col1,
  CASE WHEN table1.col_number >= 2 THEN table2.col2 ELSE NULL END AS col2,
  CASE WHEN table1.col_number >= 3 THEN table2.col3 ELSE NULL END AS col3
FROM table1 JOIN table2 USING (id);

Upvotes: 1

Related Questions