How to create a select query that ignores rows that have a certain reference to a 3rd table?

Question

I have three tables, let's say they are fruit, fruit_data and data. A row in the fruit table may have 0-n data references, and these are made using the fruit_data table:

   fruit           fruit_data        data
----------------------------------------------
|  fruit_id    |   fruit_id     |    data_id |
|  ...         |   data_id      |    name    |
|  ...         |                |    ...     |

I'd like to select all fruit.fruit_ids that do not have a reference to a data row where data.name is "glass".

I've come up with:

SELECT DISTINCT fruit.fruit_id
FROM fruit
JOIN fruit_data ON fruit_data.fruit_id = fruit.fruit_id
JOIN data ON data.data_id = fruit_data.data_id
WHERE data.name != 'glass';

but this seems to exclude fruit rows that do not have any references to data.

So, how to write a query that returns all fruit_ids that either don't have any data rows or that only have data rows where data.name != "glass"?

score 0 · Answer 1 · answered Sep 08 '21 at 16:03

One method is left joins and group by:

SELECT f.fruit_id
FROM fruit f LEFT JOIN
     fruit_data fd
     ON fd.fruit_id = f.fruit_id LEFT JOIN
     data d
     ON d.data_id = fd.data_id 
GROUP BY f.fruit_id
HAVING SUM(CASE WHEN d.name = 'glass' THEN 1 ELSE 0 END) = 0;

But I think not exists is a more sensible method:

SELECT f.fruit_id
FROM fruit f 
WHERE NOT EXISTS (SELECT 1
                  FROM fruit_data fd
                       data d
                       ON d.data_id = fd.data_id 
                  WHERE fd.fruit_id = f.fruit_id AND
                        d.name = 'glass'
                 );

score 0 · Answer 2 · answered Sep 08 '21 at 16:04

You could use the not exists operator:

SELECT *
FROM   fruit f
WHERE  NOT EXISTS (SELECT *
                   FROM   fruit_data fd
                   JOIN   data d ON fd.data_id = d.data.id
                   WHERE  fd.fruit_id = f.fruit_id AND
                          d.name = 'glass')

How to create a select query that ignores rows that have a certain reference to a 3rd table?

2 Answers2