CODEWITHSUNDEEP
sqlpostgresqljoin
HelloWorld
HelloWorld

Reputation: 1863

Two inner joins return empty result

I have 3 very simple tables, users, projects and starred_projects.

starred_projects connects the other 2 other tables by having a user id and a project id.

CREATE TABLE projects
(
    project_id integer,
    name character varying(50) COLLATE pg_catalog."default",
    creator_id integer,
)

CREATE TABLE users
(
    user_id integer,
    username character varying(64) COLLATE pg_catalog."default" NOT NULL,
)

CREATE TABLE starred_projects
(
    user_id integer NOT NULL,
    project_id integer NOT NULL
)

For a given user (e.g. "foo") I would like to display all projects he starred. Is an INNER JOIN the correct way to go here? When I do so, my result is always empty. As far as I remember from university, I would need two of them, as in:

SELECT
   p.name, u.username // display the name of the project and the user who created it
FROM
   projects p
INNER JOIN
   starred s
ON
   p.creator_id = s.user_id
INNER JOIN
   users u
ON
   u.user_id = p.creator_id
WHERE
   u.username = 'foo'; // display all starred projects of user foo

Can anyone help me on this?

Upvotes: 0

Views: 76

Answers (2)

AminM
AminM

Reputation: 856

Users aren't necessarily creators.

A more correct way would be:

SELECT
   p.name, u.username // display the name of the project and the user who created it
FROM
   projects p
INNER JOIN
   starred s
ON
   p.project_id = s.project_id
INNER JOIN
   users u
ON
   u.user_id = s.user_id
WHERE
   u.username = 'foo';

In other words, the creator of the project is not necessarily the only user. So you use starred to figure out which users are starred in what projects. So you match starred.user_id with user.user_id and you match starred.project_id with project.project_id.

Users with users, and projects with projects.

Upvotes: 2

Adamski443
Adamski443

Reputation: 21

You have your table name as starred_projects yet in your INNER JOIN you quote it as just starred. Is that correct?

I can't see anything else wrong with your code, are you able to provide examples of the data in each table?

Upvotes: 2

Related Questions