Postgres SQL Joins for Many To Many Relationship

Question

right now I am "learning" Postgres SQL. I have 3 tables:

1) User: userId

2) Stack :stackId

3) User_Stack: userId, stackId

Now I want to fetch all stacks belonging to one user, given the userId. I understand I need to use Joins, but thats were I get stuck... I try it like this:

SELECT * FROM "Stack" LEFT OUTER JOIN "User_Stack" ON ('User_Stack.stackId' = 'Stack.stackId') WHERE "userId" = '590855';

Error: The returned data is empty.

PS: Is there any GUI Query builder out there ? Or do you have any other tips how to systematically create queries ?

EDIT: If I change the query to this:

SELECT * FROM "Stack" INNER JOIN "User_Stack" ON (User_Stack.stackId = Stack.stackId) WHERE "userId" = '590855';

I get the following error:

  Kernel error: ERROR:  missing FROM-clause entry for table "user_stack"
  LINE 1: SELECT * FROM "Stack" INNER JOIN "User_Stack" ON (User_Stack...

Please always add the errors you're getting etc. Some hints to start: don't use single quotes in numeric values. Don't use double quotes for field and table names unless you actually have then case sensitively in upper case. Don't use single quotes in field names. Make sure the join type is what you want. — Sami Kuhmonen
– Sami Kuhmonen, Commented Aug 13, 2015 at 10:13
pgAdmin is the standard PostgreSQL GUI that's distributed together with the server. If suddenly you don't have it, separate downloads are available. — vitaly-t
– vitaly-t, Commented Aug 14, 2015 at 11:47

Sami Kuhmonen · Accepted Answer · 2015-08-13 10:18:09Z

1

Your main error is in the join. If you do 'something' = 'other' you're comparing string literals, not getting anything from the database. So this will always return false. You will want to compare table1.field1 = table2.field2

Another thing is the LEFT OUTER JOIN. I'm pretty sure you want an INNER JOIN since you want only fields that exist in the other table.

Also don't use double quotes for fields and table names since then the database will require case sensitivity and usually it's not good to have case sensitive names. You can use them with lowercase names if you need and always create them in lowercase.

Numbers also don't need to be quoted, it will just cause more processing when the system has to convert them from text to numbers.

answered Aug 13, 2015 at 10:18

Sami Kuhmonen

31.4k9 gold badges67 silver badges81 bronze badges

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

Collectives™ on Stack Overflow

Postgres SQL Joins for Many To Many Relationship

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related