Writing new SQL query with relation to previous query results

[RoyAsh]

Hi,
I am writing PBI queries that combine data from different databases (SQL Server and Netezza Server).

I know how to write single/seperate query from each DB. I am trying to write new SQL query, with join (or where) that relates to single field from the first SQL.
For example: from the first DB I customers' proposals.

And now, I need to retreive from the other DB the customers' personal infromation - based on the customer ID from the first query.

I didn't succeed to find out how to write it....

thanks in advance for you help.
Roy

[v-yohua-msft]

Hi, @RoyAsh 

The most straightforward way to do this is to use Power BI's Power Query Editor to connect to the two databases separately, load the data, and then perform a merge operation in Power BI. Connect to both databases separately in Power BI Desktop. For Netezza, the process is similar, but requires the appropriate connectors. Merge queries in Power Query Editor.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RoyAsh]

First, thanks for the answer!

But, unfortunately, this doesn't answer the question why I originally asked it. Let me esplain the issue: I do know how to retreive from each DB separately. No problem with that. That was not my origianl question.

The issue is as follows: from the first DB I extract business infromation. For example: all customers who purchased a certain product. Now, from the second DB, which contains customers' details, I want to rereive the full customers' details. Since, there is, relatively speaking, a small number of customers who purchased the product compared to the number of customers in the second DB, I want to build 2nd SQL queries in the following style:

---- first query -------

select customer_id

into result1

from db1.product_purchased

where product_date between '2024-01-01' and '2024-04-01'

----------

Now, I want to retrieve customers' details from the second DB, but only of the customers who purchased, so write a query such:

--------- second query from second DB ------------

select *

from db2.customer_information

      inner join  result1 on customer_information.customer_id = result1.customer_id

-----------------------

My question is: how to reference and write, within the second SQL code the reference to "result1.customer_id", which is the results of the first retrieval.

It doesn't make sense (in my opinion) to pull out a relatively large amount of customers' data, and only than do, as a third part/query, a merge query.

Is there a way to do this efficiently by write SQL code in PBI?

Regards,
Roy