Postgres Query to join two tables

Question

I have two tables in Postgres database. In each table there is a column which represent same number. I have tried few join queries to join both tables with similar column numbers but none of them are giving me the expected output.

user_id column from Table 1 is equal to Id column in Table 2

How can join these two tables?

I have tried below and some other queries as well but it didn't get what I wanted

SELECT members.access_level, members.user_id FROM members INNER JOIN users ON members.user_id = users.id;

Tables columns looks like below,

Members table

id |access_level |source_id |user_id |type

Users Table

id |email |name |username

Query output should look as below:

username |name |email |access_level

The query you described seems like it should work. When you say you're not getting the expected output, do you mean that data from the two tables does not appear to match or that you don't see the columns you want? — BillRobertson42
– BillRobertson42, Commented Feb 7, 2019 at 19:31

J Spratt · Accepted Answer · 2019-02-07 20:37:13Z

6

SELECT  u.username
        , u.name
        , u.email
        , m.access_level
FROM users u
    JOIN members m ON (u.id = m.user_id)
;

If you want users that are not included in the members table you can join with a LEFT JOIN

To address your question asked in the comments I believe you'd be looking for something like the following:

UPDATE members SET access_level = 'dev' WHERE access_level = '30';

This is assuming that the column is already of type text. Otherwise, you'll need to change the data type first using the following:

ALTER TABLE members
    ALTER access_level SET DATA TYPE text;

edited Feb 7, 2019 at 20:37

answered Feb 7, 2019 at 19:51

J Spratt

2,0521 gold badge16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Prs Over a year ago

Thank you...In the access_level column there are predefined numbers for entire column like 30, 40, 50 ...I want replace these numbers with string such as Dev, Main, Owner..Is this something possible with query?

J Spratt Over a year ago

Do you only want to update the values for members that are also users or all members? Also, what is the data type of the access_level column currently? It's likely you'll want an UPDATE query.

Prs Over a year ago

access_level column is in members table and its numeric data type..it has 30,40 and 50 values ..I want them to replace with Dev, Main and Owner

BillRobertson42 · Accepted Answer · 2019-02-07 19:27:55Z

1

 SELECT users.*, members.user_id, members.acces_level 
 FROM   members 
 LEFT JOIN users 
 WHERE users.id = members.user_id

edited Feb 7, 2019 at 19:27

BillRobertson42

12.9k4 gold badges44 silver badges62 bronze badges

answered Feb 7, 2019 at 18:52

Maulana syarief

111 bronze badge

1 Comment

Aaron Over a year ago

Thank you, the [table].* syntax was just what I needed.

Collectives™ on Stack Overflow

Postgres Query to join two tables

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related