SQL query in two tables - returns rows in one table that do not have records in another table

Question

I have two database tables, Categories and Supercategories, for the inventory management system I'm working on:

Categories: ID_Category, CategoryName

SuperCategories: ID_SuperCategory, CategoryID, SuperCategoryID

I put subcategory category relationships in the SuperCategories table. I put all categories in a category table.

Here is an example:

    Categories:
    ID_Category CategoryName
    1 Box
    2 red box
    3 blue box
    4 Blue Plastic Box
    5 can
    6 tin can  

    
    SuperCategories:
    ID_Super CategoryID SuperCategoryID
    1 2 1
    2 3 1
    3 4 3
    4 6 5

CategoryID SuperCategoryID ID_Category "".

, , , - :

Tin Can

ID_Category, SuperCategoryID (2, 4 6), SQL.

VB6 Access 2000.

. !

EDIT: , -, . , , , . !

Answer 1

, "" "SuperCategories" "LEFT OUTER JOIN" - "" "" , - , (, "SuperCategories" ), NULL SuperCategories, , .

:

SELECT c.CategoryName, s.ID_Super 
FROM Categories c 
LEFT OUTER JOIN SuperCategories s ON c.ID_Category = s.SuperCategoryID

- :

CategoryName    ID_Super
Box               1
Box               2
Red Box           NULL
Blue Box          3
Blue Plastic Box  NULL
Can               4
Tin Can           NULL

, - , ID_Super LEFT OUTER JOIN NULL - , SuperCategories. ?: -)

Answer 2

SELECT
     CAT.ID_Category,
     CAT.CategoryName
FROM
     Categories CAT
WHERE
     NOT EXISTS
     (
          SELECT
               *
          FROM
               SuperCategories SC
          WHERE
               SC.SuperCategoryID = CAT.ID_Category
     )

SELECT
     CAT.ID_Category,
     CAT.CategoryName
FROM
     Categories CAT
LEFT OUTER JOIN SuperCategories SC ON
     SC.SuperCategoryID = CAT.ID_Category
WHERE
     SC.ID_Super IS NULL

, , , - . .

Answer 3

, .

select CategoryName from Categories LEFT OUTER JOIN
SuperCategories ON Categories.ID_Category =SuperCategories.SuperCategoryID
WHERE SuperCategories.SuperCategoryID is  null

Answer 4

, Access, - :

select CategoryName from Categories
where ID_Category not in (
    select SuperCategoryID 
    from SuperCategories 
)

Answer 5

, marc_s. OUTER JOINS . , .

ISNULL, , A B, ISNULL .

 SELECT 
       isNull(a.[date_time],b.[date_time]) as [Time Stamp]
      ,isnull(a.[ip],b[ip]) as [Device Address]
      ,isnull(a.[total_messages],0) as [Local Messages]
      ,isnull(b.[total_messages],0) as [Remote Messages]
  FROM [Local_FW_Logs] a
FULL OUTER JOIN [Remote_FW_Logs] b 
on b.ip = a.ip

Answer 6

I have two tables interface_categoryand interface_subcategory.

interface_subcategory contains SubcategoryID, CategoryID, Name(SubcategoryName)

interface_category contains CategoryID, Name(CategoryName)

Now I want the output CategoryIDand name (subcategory name)

The request that I wrote below and its work for me

select ic.CategoryID, ic.Name CategoryName, ISC.SubCategoryID, ISC.Name SubCategoryName from Interface_Category IC
inner join Interface_SubCategory ISC
on ISC.CategoryID = ic.CategoryID
order by ic.CategoryID, isc.SubCategoryID