Linking of 2 tables


#1

I want to join 2 tables in order get all columns of table B in the table A.


#2

Hi Hardik,

I’d recommend using a native SQL query and writing the join manually. As far as I know, Joins are difficult to do in the UI question builder. See this article for reasons why: https://www.metabase.com/blog/Joins/


#3

Your joins should be defined in the metabase data model using the foreign key. Look at the sample database for how it works./


#4

To add to what’s already been said.

There’s currently a limitation with @AndrewMBaines suggestion about metadata setup of Foreign and Entity key links is the that you can’t show other target table fields than the key and fields you group by. I would still recommend making the metadata setup because it makes the query builder UI much nicer with you can pick related fields and see from what underlying table it comes from.

This limitation is covered here:

With @omegadeep10’s suggestion there’s a limitation on table views where I think you will loose the interactive sorting options on fields and the ability to define calculated fields. This can be worked around with https://www.metabase.com/blog/Nested-Queries/

update You can see nice GIFs (and who doesn’t want GIFs :rofl:) illustrating both features here:


#5

You can meet the requirements of the original request by grouping on all the fields you need and displaying raw data. It’s not ideal, but it works.
This is from the sample data. I’ve not included all columns for brevity, but this lists the product groups, customer names and customer source. You need to keep it as raw data or you end up with a cross tab.


#6

WOW! seems v0.30 got UX improvements on this: https://metabase.com/blog/0.30/index.html#add-related-columns-in-tables - so the “raw data + grouping workaround” in Andrew’s post just above isn’t required anymore.