1

Say I have a table C that references rows from tables A and B:

id, a_id, b_id, ...

and a simple query:

SELECT * FROM C WHERE a_id=X AND b_id=Y

I would like to differentiate between the following cases:

  • No row exists in A where id = X
  • No row exists in B where id = Y
  • Both such rows in A and B exist, but no rows in C exist where a_id = X and b_id = Y

The above query will return empty result in all those cases.

In case of one parent table I could do a LEFT JOIN like:

SELECT * FROM A LEFT JOIN C ON a.id = c.a_id WHERE c.a_id = X

and then check if the result is empty (no row in A exists), has one row with NULL c.id (row in A exists, but no rows in C exist) or 1+ rows with non-NULL c.id (row in A exists and at least one row in C exists). A bit messy but it works, but I was wondering if there is a better way of doing this, especially if there is more than one parent table?

1

I think I managed to get a satisfactory solution using the following two features:

  • Subselect bound to a column, which allows me to check if a row exists and (importantly) get a NULL value otherwise (e.g. SELECT (SELECT id FROM a WHERE id = 1) as a_id))

  • Common Table Expressions

Initial data:

CREATE TABLE people 
(
  id integer not null primary key, 
  name text not null
);

CREATE TABLE thing_types 
(
  id integer not null primary key, 
  name text not null
);

CREATE TABLE things
(
  id integer not null primary key, 
  person_id integer not null references people(id), 
  thing_type_id integer not null references thing_types(id), 
  name text not null
);

INSERT INTO people VALUES (1, 'Bill');
INSERT INTO thing_types VALUES (1, 'game');

INSERT INTO things VALUES (1, 1, 1, 'Duke Nukem');
INSERT INTO things VALUES (2, 1, 1, 'Warcraft 2');

And the query:

WITH v AS (
  SELECT (SELECT id FROM people WHERE id=<person_id_param>) AS person_id, 
         (SELECT id FROM thing_types WHERE id=<thing_type_param>) AS thing_type_id
)
SELECT v.person_id, v.thing_type_id, things.name 
FROM 
  v LEFT JOIN things 
    ON v.person_id = things.person_id AND v.thing_type_id = things.thing_type_id

This query will always return at least one row. In case if both parent table ids are valid and there are some records, I get

person_id  thing_type_id  name
-------------------------------------
        1              1   Duke Nukem
        1              1   Warcraft 2

If either person_id or thing_type_id are invalid, I get one row where name is NULL and either person_id or thing_type_id is NULL:

person_id  thing_type_id  name
-------------------------------------
     NULL              1         NULL

If both person_id and thing_type_id are valid but there are no records in things, I get one row where both person_id and thing_type_id are not NULL, but the name is NULL:

person_id  thing_type_id  name
-------------------------------------
        1              1         NULL

Since I have a NOT NULL constraint on things.name, I know that this case can only mean that there are no matching records in things.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.