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.