Joining Tables with a SQL Query on QS

Hi! I am trying to join two tables using custom SQL on the dataset page inside QS.
I need to be able to see every column from both tables. However, the two tables have a lot of columns that have the same names and QS gives me the error on the image below.

Is QS not able to rename the columns that have the same name to maybe also include the table name like it does in analysis?

Selecting specific columns to pull is not an option given to the large number of columns on each of the tables.

Is there any way to deal with the duplicates across these two tables using a SQL query? (I know it is possible to do this not using SQL queries)

Hello @mateoleon210 - Happy New Year! It is always recommended in BI tools (including QuickSight) to select specific columns instead of using the * wildcard. As you mentioned, it could be a cumbersome process to write down the SQL; however you can use simple Excel formulas or leverage database internal system/metadata tables to easily create the column structure automatically and then build your SQL. That will perhaps help you to save the time and effort (that you are trying to save) as well as follow the best practices of specifying columns rather than doing “SELECT *”. I have tried to provide a sample of the same for your reference. Hope this helps!

SELECT
– Creates the list of column names in tablename_columnname format
– and appended with a comma
t.name + ‘_’ + c.name + ‘,’ as columnname
FROM sys.tables t
LEFT JOIN sys.columns c
on t.object_id = c.object_id
– change the table name to obtain the list of columns for that table
where t.name = ‘DimCustomer’

1 Like