当前位置: 动力学知识库 > 问答 > 编程问答 >

node.js - Postgres SQL Joins for Many To Many Relationship

问题描述:

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...

网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: