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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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);


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x