Join table where the id doesn't exist

Question

I want to do a SELECT that gets the data from a table and also JOINS the information from a table where the d.demographic_category_id from demographic_data doesn't exist in demographic_category column c.demographic_category_id

So it's something like this:

      SELECT *
      FROM demographic_data d
      INNER JOIN demographic_info i
      ON d.demographic_info_id = i.demographic_info_id
      AND i.student_id = 1
      LEFT JOIN demographic_category c
      ON d.demographic_category_id
      NOT IN ( select c.demographic_category_id from demographic_category c);

What i want to do is to get the demographic_data which is being done correctly with this statement.

SELECT *
      FROM demographic_data d
      INNER JOIN demographic_info i
      ON d.demographic_info_id = i.demographic_info_id
      AND i.student_id = 1

BUT THEN grab the data from demographic_category WHERE the demographic_data.demographic_category_id DOESN'T exist IN demographic_category, so therefore grab the categories that doesn't exist in the ids from the query from above this paragraph.

Gordon Linoff · Answer 1 · 2020-07-29T01:01:32.767

You can use a WHERE clause. I would recommend NOT EXISTS:

  SELECT *
  FROM demographic_data d JOIN
       demographic_info i
       ON d.demographic_info_id = i.demographic_info_id AND
          i.student_id = 1
  WHERE NOT EXISTS (SELECT 1
                    FROM demographic_category c
                    WHERE d.demographic_category_id = c.demographic_category_id
                   );

It seems quite odd to me that you would have a column that doesn't match, however. If you have a foreign key relationship defined, then all the values (except for NULL of course) will match.

EDIT:

Based on your comment, you seem to just want a LEFT JOIN:

  SELECT *
  FROM demographic_data d JOIN
       demographic_info i
       ON d.demographic_info_id = i.demographic_info_id AND
          i.student_id = 1 LEFT JOIN
       demographic_category c
       ON d.demographic_category_id = c.demographic_category_id;

Yeah i've tried but to no avail. it's because what i want is to get all of the demographic_data info that fullfills the first join that you have. BUT ALSO bring the data from the demographic_category that does not exist in demographic_data table so thing like d.demogrsphic_category_id NOT IN (SELECT c.demographic_category_id FROM demographic_category c WHERE d.demographic_category_id = c.demographic_category_id ) — JustAJavaUser, Jul 28 '20 at 21:55

Join table where the id doesn't exist

1 Answers1