# Joins From 2 Tables In Supabase **URL:** \ **Category:** Ask us anything\ **Created:** [September 28, 2023, 9:23pm UTC](https://community.weweb.io/t/joins-from-2-tables-in-supabase/4647 "2023-09-28T21:23:16Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![Jonny](https://avatars.discourse-cdn.com/v4/letter/j/47e85d/32.png) [@Jonny](https://community.weweb.io/u/Jonny)\ **Post date:** [September 28, 2023, 9:23pm UTC](https://community.weweb.io/t/joins-from-2-tables-in-supabase/4647/1 "2023-09-28T21:23:16Z")
Hey guys! @Broberto @Joyce Elementary questions here… What is the best way to get another table with a foreign key from another (AKA Joins) form supabase? Example, a table called “users” has a user ID integer. That user id integer is a foregin key in a table called “accounts”, and I want to get the account ID based upon that user ID integer. I know its simple, but just need to be pointed in the best way to do this within weweb. Currently I am changing variables frequently and I am noticing its not the most ideal method. I assume you will recommend an array formula, and if thats the case do you have an example that I can go by and replicate that for all the joins id like to make? ---
**Author:** ![Broberto](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/broberto/32/5918_2.png) [@Broberto](https://community.weweb.io/u/Broberto)\ **Post date:** [September 28, 2023, 9:30pm UTC](https://community.weweb.io/t/joins-from-2-tables-in-supabase/4647/2 "2023-09-28T21:30:38Z")
Hey, thanks for the mention, the easiest way (at the moment) is to go to **Supabase → Dashboard → SQL → SQL Editor** and doing the following query ![Screenshot 2023-09-28 at 23.30.00](https://us1.discourse-cdn.com/flex016/uploads/weweb/original/2X/8/8528717f26a83bfbc93816359e8a4d7c3b47cd7e.png) ```plaintext CREATE VIEW users_foreign_key_account -- Your View Name AS SELECT * -- Your desired columns from each joined table -- Here begins the JOIN tables part, so you're selecting FROM joined table .. FROM users -- This is the first table, in your case would be users JOIN accounts ON users.id = accounts.account_id -- Now you join it with your table on your foreign key (I suppose it's accounts.account_id) ``` Then you just call the view from Supabase plugin (don’t forget to refresh the tables) You could do this via **Advanced** in the Supabase plugin by doing something like this ```auto *,accounts:id(*) ``` like this, but it’s error prone, and it will most certainly throw you an error, because id is too ambiguous. If you give me the error, I can tell you how the query is supposed to look like. ![Screenshot 2023-09-28 at 23.33.38](https://us1.discourse-cdn.com/flex016/uploads/weweb/original/2X/5/5e0279b9bd851b0f010cea3d9d923b1861cedad2.png) I’d still go for the first approach with the VIEWs as for now, until the plugin gets updated, you **can not filter the result of the query via Advanced** , this is a bug/limitation. So if you want to filter your JOINs (that’s what this “usage” of foreign key references is called), you go with views. ---
**Author:** ![Jonny](https://avatars.discourse-cdn.com/v4/letter/j/47e85d/32.png) [@Jonny](https://community.weweb.io/u/Jonny)\ **Post date:** [September 28, 2023, 9:37pm UTC](https://community.weweb.io/t/joins-from-2-tables-in-supabase/4647/3 "2023-09-28T21:37:11Z")
@Broberto Thanks so much sounds good. How that would show up in Weweb for example as a collection? Would all of the variables from the 2 databases be joined into 1 collection response? As in liek this still"? ![Screenshot 2023-09-28 at 4.36.49 PM](https://us1.discourse-cdn.com/flex016/uploads/weweb/original/2X/6/6e0b746483485d7569e88cde02410956993f790c.png) ---
**Author:** ![Broberto](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/broberto/32/5918_2.png) [@Broberto](https://community.weweb.io/u/Broberto)\ **Post date:** [September 28, 2023, 9:43pm UTC](https://community.weweb.io/t/joins-from-2-tables-in-supabase/4647/4 "2023-09-28T21:43:51Z")
Yes, a JOIN basically joins two tables, ON the foreign key relationship. _So if you have_ ## users **id** , username, email, password _and_ ## accounts **user\_id** , name, surname, phone _If you JOIN these two ON users.id = accounts.user\_id, you get a table looking like this_ ## users\_accounts **id int8** , username, email, password **user\_id int8** , name, surname, phone ---
**Author:** ![Broberto](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/broberto/32/5918_2.png) [@Broberto](https://community.weweb.io/u/Broberto)\ **Post date:** [September 28, 2023, 9:48pm UTC](https://community.weweb.io/t/joins-from-2-tables-in-supabase/4647/5 "2023-09-28T21:48:57Z")
I see you’re dealing with API keys, don’t forget to set up your [Row Level Security | Supabase Docs](https://supabase.com/docs/guides/auth/row-level-security) ---
**Author:** ![Jonny](https://avatars.discourse-cdn.com/v4/letter/j/47e85d/32.png) [@Jonny](https://community.weweb.io/u/Jonny)\ **Post date:** [September 29, 2023, 8:19pm UTC](https://community.weweb.io/t/joins-from-2-tables-in-supabase/4647/6 "2023-09-29T20:19:22Z")
@Broberto I have removed row level security. I’m not 100% sure If I have set htis up right, as the VIEW is not getting any data. So, it has slightly changed from when I posted this originally, but here is the SQL that you gave me customized. Note: The table is called “user access” and its sole purpose it do denote a users connection to an account. ![Screenshot 2023-09-29 at 3.12.51 PM](https://us1.discourse-cdn.com/flex016/uploads/weweb/original/2X/9/901e12cc4802bf75d4e2da63f8ad2a68e13b4dac.png) I now see the view in Weweb. HOWEVER, in weweb, it is not finding any relationships between the user\_access table and the accounts table, and there is definitely supposed to be at least 1 showing up. Im getting this error: ![Screenshot 2023-09-29 at 3.17.57 PM](https://us1.discourse-cdn.com/flex016/uploads/weweb/original/2X/1/1e022fd1e977d2102d9eed9c9f890dccabaa6f8f.png) ---
**Author:** ![Broberto](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/broberto/32/5918_2.png) [@Broberto](https://community.weweb.io/u/Broberto)\ **Post date:** [September 29, 2023, 8:23pm UTC](https://community.weweb.io/t/joins-from-2-tables-in-supabase/4647/7 "2023-09-29T20:23:40Z")
Could you copy and paste the whole error? Does the joined view show properly in Supabase? If you sent me your tables structure and how they’re referenced, that would be cool, no need to send data, just the columns ---
**Author:** ![Jonny](https://avatars.discourse-cdn.com/v4/letter/j/47e85d/32.png) [@Jonny](https://community.weweb.io/u/Jonny)\ **Post date:** [September 29, 2023, 8:28pm UTC](https://community.weweb.io/t/joins-from-2-tables-in-supabase/4647/8 "2023-09-29T20:28:14Z")
@Broberto I mean it created a “view” in supabase but there is nothing in that view… “TypeError: Cannot read properties of undefined (reading ‘join’) at [https://cdn.weweb.io/components/f9ef41c3-1c53-4857-855b-f2f6a40b7186/9a220346-be3a-4300-ad9e-20b7db7e5c17/dist/manager.js:1:105715](https://cdn.weweb.io/components/f9ef41c3-1c53-4857-855b-f2f6a40b7186/9a220346-be3a-4300-ad9e-20b7db7e5c17/dist/manager.js:1:105715) at Array.map () at Oe ([https://cdn.weweb.io/components/f9ef41c3-1c53-4857-855b-f2f6a40b7186/9a220346-be3a-4300-ad9e-20b7db7e5c17/dist/manager.js:1:104958](https://cdn.weweb.io/components/f9ef41c3-1c53-4857-855b-f2f6a40b7186/9a220346-be3a-4300-ad9e-20b7db7e5c17/dist/manager.js:1:104958)) at Object.fetchCollection ([https://cdn.weweb.io/components/f9ef41c3-1c53-4857-855b-f2f6a40b7186/9a220346-be3a-4300-ad9e-20b7db7e5c17/dist/manager.js:1:106527](https://cdn.weweb.io/components/f9ef41c3-1c53-4857-855b-f2f6a40b7186/9a220346-be3a-4300-ad9e-20b7db7e5c17/dist/manager.js:1:106527)) at Object.\_fetchCollection ([https://editor-cdn.weweb.io/public/js/index.782b429f.js:1270:35718](https://editor-cdn.weweb.io/public/js/index.782b429f.js:1270:35718)) at Object.fetchCollection ([https://editor-cdn.weweb.io/public/js/index.782b429f.js:1270:36454](https://editor-cdn.weweb.io/public/js/index.782b429f.js:1270:36454)) at async Object.syncCollection ([https://editor-cdn.weweb.io/public/js/index.782b429f.js:1270:38112](https://editor-cdn.weweb.io/public/js/index.782b429f.js:1270:38112)) at async Proxy.sync ([https://editor-cdn.weweb.io/public/js/index.782b429f.js:1388:135319](https://editor-cdn.weweb.io/public/js/index.782b429f.js:1388:135319)) at async Proxy.saveConfig ([https://editor-cdn.weweb.io/public/js/index.782b429f.js:1388:134232](https://editor-cdn.weweb.io/public/js/index.782b429f.js:1388:134232))” message: “Cannot read properties of undefined (reading ‘join’)” ---
**Author:** ![Broberto](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/broberto/32/5918_2.png) [@Broberto](https://community.weweb.io/u/Broberto)\ **Post date:** [September 29, 2023, 8:35pm UTC](https://community.weweb.io/t/joins-from-2-tables-in-supabase/4647/9 "2023-09-29T20:35:31Z")
1. Do the tables exist under those names? Would be cool to be able to see them. 2. Isn’t RLS blocking your JOIN somehow? 3. It would be cool to be able to see the query as well how you’re calling it in WeWeb. Don’t you have something in advanced tab that is wrong? ---
**Author:** ![Jonny](https://avatars.discourse-cdn.com/v4/letter/j/47e85d/32.png) [@Jonny](https://community.weweb.io/u/Jonny)\ **Post date:** [September 29, 2023, 9:34pm UTC](https://community.weweb.io/t/joins-from-2-tables-in-supabase/4647/10 "2023-09-29T21:34:07Z")
@Broberto give me a sec Ill get all of this for you ---
**Author:** ![Jonny](https://avatars.discourse-cdn.com/v4/letter/j/47e85d/32.png) [@Jonny](https://community.weweb.io/u/Jonny)\ **Post date:** [September 29, 2023, 9:40pm UTC](https://community.weweb.io/t/joins-from-2-tables-in-supabase/4647/11 "2023-09-29T21:40:56Z")
Ok @Broberto I think my mistake was adding the DB’s into supbase all with a capital letter at the front so I have “case sensitivity” issues LOL! So here is what happened. I tried to use the supabase AI generator to fix it and it must have created a new table called “accounts” in lower case in the process. It was referencing that (which was empty)… My solution was to go ahead and change the SQL to the correct case and it seems to be finding a match now! Sorry for that confusion (hindsight, I should have made all of my DB’s in all lowercase)… ---
**Author:** ![Broberto](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/broberto/32/5918_2.png) [@Broberto](https://community.weweb.io/u/Broberto)\ **Post date:** [September 30, 2023, 7:33am UTC](https://community.weweb.io/t/joins-from-2-tables-in-supabase/4647/12 "2023-09-30T07:33:48Z")
That was my guess. Capital letters in SQL are no good 🙂