How can I left join two tables together?

Baserow Help
#1

Hey guys,

how can I left join two tables together?

Example: I have one table called Users and another called skills which contains a user id value for each row. How can I now automatically show all skills in a multi-select field in the users table?

user table
user id | name
1 | Daniel
2 | Peter

skills table:
id | name | user
1 | Climbing | 1
2 | Running | 1
3 | Swimming | 2

Ideal outcome in user table
user id | name | skills
1 | Daniel | Climbing, Running
2 | Peter | Swimming

I am bit puzzled as this would be a simple left join SQL statement in a database. What am I missing here?

#2

Hey there,

In Baserow you would do this the other way around.

So instead of adding the user-id to the skills table you add the skills to the user table.

You can do this with a Link Row Field and just select the skills for each user for each row.

Alternatively if you already have a link row field from skills to user, you can set a flag in the field options that creates a corresponding link row field in the other table.

Under the hood these link row fields are represented by a many-to-many table in postgres :slight_smile:

If you then want to display a different column of the skills table in the user table you can use a Lookup field.

Hope that helps!

#3

Understood but can I somehow make use of the fact that the relation is already defined by the user column in the skills table? In my case it would not be feasible to manually select all matching rows for each user.

#4

Hey @Daniel ,

How did those ids end up in the column in the first place?

If they are manually typed in it shouldn’t take much more time to select the relation instead.

If they are entered through an automation I’d suggest adjusting the automation to make a link instead of inserting the ID.

If you don’t have an automation yet you can also create one on n8n, zapier or make that can turn these ids into relations.

Baserow by itself does not have automatic functionality to turn numbers into relations.

#5

Hi Alex,

thanks for getting back to me so quickly! I am indeed populating the database via APIs. As I am also using n8n for some other stuff, I will look into turning these ids into proper relations automatically (I guess the awesome baserow API documentation will help me out). You can close this thread.

Thx for your help!
Daniel