Write SQL Query from an output tables of SQL

[JustDavid]

Hello,

I have 4 tables which is an output from Sql.Database ("server", "database", [Query=sql statement...])

I tried using the 'Merge Queries' function but the result is too slow to generate output and have errors as well (i've checked on the output tables, there weren't any errors).

Now I'm trying to write a SQL (like how I've written above using SELECT statement) to join these output tables.

Is it possible? And if it does, how do I write these SQL statement to join these tables?

How do I do that?

[v-cgao-msft · Accepted solution]

Hi @JustDavid ,

From the description, the SQL statement looks similar to something like this:

SELECT 
    t1.*, 
    t2.*, 
    t3.*, 
    t4.*
FROM 
    (table1 sql statement) t1
JOIN 
    (table2 sql statement) t2 ON t1.key = t2.key
JOIN 
    (table3 sql statement) t3 ON t1.key = t3.key
JOIN 
    (table4 sql statement) t4 ON t1.key = t4.key;

You need to modify the JOIN type as well as the JOIN condition accordingly.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Ray_Minds]

Hi @JustDavid 
1. We need to pull the data as Table formate
2. Column Data Type should be same based on the merging table .
3. then apply joins as per requirement.

SQL Solution :-
1. Connect to the SQL Server and DataBase
2. Select the Data Connectivity Mode(Import or Direct Query)
3. Write the SQL join Statement in the SQL statement Dialog Box
4. Join Statement
SELECT A.Column1,A.Column2 ,B.Column1,B.Column2
FROM Table1 A
LEFT JOIN Table2 B
ON A.Column1 = B.Column1
5. Click On ok

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

[JustDavid · Accepted solution]

@Ray_Minds That's what I had. And in my original thread post above, I had 4 output tables.

My question is that on a 5th table, I'd like to use SQL Statement to JOIN those 4 output tables. However before joining them, I'd need to further filter out unncessary data.

If I were to use Power Query's 'Merge' built-in function, it's giving me very slow "refresh" (and thus output). Not to mention too that, I need to further filter out the 4 output tables. Thus the question if there's a possibility of using SQL instead of Power Query built-in 'Merge'.

I guess the answer is no, and that I had to make all of my SQL statements into 1.

[JustDavid]

I see...so in short, I have to write all the SQL queries that I've made in those 4 "tables" output into 1 big query then.

I was thinking of having it like if I'm using SSMS where I create multiple views/tables and then in a new SQL query, to then join them.

[v-cgao-msft · Accepted solution]

Hi @JustDavid ,

From the description, the SQL statement looks similar to something like this:

SELECT 
    t1.*, 
    t2.*, 
    t3.*, 
    t4.*
FROM 
    (table1 sql statement) t1
JOIN 
    (table2 sql statement) t2 ON t1.key = t2.key
JOIN 
    (table3 sql statement) t3 ON t1.key = t3.key
JOIN 
    (table4 sql statement) t4 ON t1.key = t4.key;

You need to modify the JOIN type as well as the JOIN condition accordingly.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum