Variable Ref in SQL Editor - Cross Two Different Datasources

Get Help
1

Hi there,
I am fairly new to Metabase, and I wondered if it’s possible to perform something like the following:

SELECT table_1.field_a, table_2.field_b
FROM datasource_1.table_1
JOIN datasource_2.table_2 On a_1=b_1
WHERE
{{date}} and {{fielddropdown}}

When the variables pane shows up, you can only set up for the single data source. If there a way to create variables which reference from two different data sources in the single query?

Date variable referencing DataSource_1
and FieldDropDown varaible to reference DataSource_2

Or is this impossible?

2

Hi @yetiboy
No, Metabase does not support queries across databases, so you’ll need to combine your different databases into one warehouse.
https://github.com/metabase/metabase/issues/3953 - upvote by clicking :+1: on the first post

3
4

Ok - I can run the query which links the two db’s fine, it’s just trying to add filtering on one table and another filter on the other table. That is the bit that it doesn’t support.

5

Rather than join across the two sources in metabase, I could perhaps do the join in a view in the datasource and then that would work.

6

@yetiboy What is it that isn’t working? There shouldn’t be any problems doing what you’re trying.
Your example doesn’t use table aliases, but it sounds like you’re using aliases, which currently isn’t supported:
https://github.com/metabase/metabase/issues/3324 - upvote by clicking :+1: on the first post

But it would help a lot if you posted which database type you’re querying. And while we’re at it, please post “Diagnostic Info” from Admin > Troubleshooting.

7

Thanks Flamber. I’ll have another look. It seems to me that when you create a field type variable, the SQL editor only looks at one Datasource. Therefore, you are only provided a list of schemas/tables etc from that source, and not the other source.

8

When I change the drop down in the datasource to be the other source, then my variable relating to the other datasource becomes corrupted. It seems like I need to combine the data in a view in either one of the db’s

9

@yetiboy Without knowing which database you’re using, then I have no idea what the problem could be.
It works for Postgres with multiple schemas, which is one database.
But I think we’re returning to 3953 - so upvote that issue and then create a database view or look at what Andrew suggested.

10

I have two SQL Server DB’s - so
SELECT database1.schema.table.field, database2.schema.table.field
FROM database1.schema.table
JOIN database2.schema.table

This all works fine, but I can only apply filtering on one of the datasources, and not the other. An either or scenario, I cannot have two variables, one pointing at database1, and one pointing at database2 in the Query Editor

11

@yetiboy That is very different than your first example. That is two databases, so issue 3953.
Create a view and sync that to Metabase.

12

Sorry, was trying to highlight I have two data sources in Metabase, which are two SQL server databases. Easily joined together in the query etc but you have to state which is the main datasource in the query editor.

So in short, you only create variables in your query relating to the single datasource, and not to anything reference in the query that is outside the scope of that datasource.

Thanks again for your time!