Skip to content
Advertisement

How to use triple INNER JOIN with one empty table?

Given is the following table:

CREATE TABLE projects
(
    project_id integer,
    name character varying(50),
    owner_user integer NOT NULL,
    owner_org integer NOT NULL,
)

A project can be either owned by a user (user_id), OR by an organization (org_id) So either field is 0. So I do an INNER JOIN on projects, users, and organizations, but if the project is owned by a user (and currently no organization does exist at all), the result is empty.

Does NOT work

SELECT *
FROM users u, projects p, organizations o
WHERE (p.owner_org != 0 AND o.org_id = p.owner_org) OR
      (p.owner_user != 0 AND u.user_id = p.owner_user);

Can anyone help me out? If I leave out organization, the result is 1 row, as expected.

Works

SELECT *
FROM projects p, users u
WHERE (p.owner_user != 0 AND u.user_id = p.owner_user);

Advertisement

Answer

Learn to use proper, explicit, standard, readable JOIN syntax.

You seem to want:

select *
from projects p left join
     users u 
     on p.owner_org = o.org_id left join
     organizations o
     on p.owner_user = u.user_id ;

I assume there is no id of 0 in the projects or organizations tables so the filtering is not necessary.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement