How to Determine if a Primary Key in Table 1 does not Appear in Table 2

Question

How to tell if primary key of one table is not in the current table

item                        (current table) category_item                                            
-------|--- ----           ----------------|------------|----------
item_id          item          category_item_id| category_id|  item_id 
2119                          1                   16           2090
        ^primary key                                     ^foreign key

I need a query that tells me that there is no record in the "category_item" table , that has the foreign key of any record table "item" category_item.item_id

In laymans terms I want to find out all record in "item" that do not have a "category" assigned to it.

This is true, where we cannot find any record in "category_item" that has the primary key of any of the records in table "ITEM" in the

category_item.item_i

The SELECT would item.item_id where get for e.g.

2113  
2110   
2310

each of which has no categories assigned yet.

Thanks @Shadow from pointing to the duplicates. WHERE [column] NOT IN. ... is the key ``` SELECT item_id. FROM item. WHERE media_type LIKE '%audio%'. AND item_id BETWEEN 4999 and 9000. AND item_id NOT IN ( SELECT item_id. FROM category_item. ). ORDER BY. item.item_id; ``` — Nathsevak, May 21 '19 at 02:01

score 0 · Answer 1 · answered May 20 '19 at 21:31

0

Do you just want not exists?

select i.item_d
from items i
where not exists (select 1 from category_item ci where ci.item_id = i.item_id);

answered May 20 '19 at 21:31

Gordon Linoff

1,242,037
58
646
786

How to Determine if a Primary Key in Table 1 does not Appear in Table 2

1 Answers1