对两个表的SQL查询 - 返回一个表中没有另一个表的行

I have two database tables, Categories and SuperCategories, for an inventory control system I'm working on:

我有两个数据库表,Categories和SuperCategories,用于我正在处理的库存控制系统:

Categories: ID_Category, CategoryName

分类:ID_Category,CategoryName

SuperCategories: ID_SuperCategory, CategoryID, SuperCategoryID

SuperCategories:ID_SuperCategory,CategoryID,SuperCategoryID

I'm putting category-subcategory relationships into the SuperCategories table. I'm putting all categories into the Categories table.

我将类别子类别关系放入SuperCategories表中。我将所有类别放入Categories表中。

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 and SuperCategoryID relate back to the primary key ID_Category in the Categories table.

CategoryID和SuperCategoryID与Categories表中的主键ID_Category相关。

What I would like is a query that returns all of the category names that are not parents of any other categories:

我想要的是一个返回所有不是任何其他类别的父类别的类别名称的查询:

Red Box
Blue Plastic Box
Tin Can

红色盒子蓝色塑料盒锡罐

This amounts to finding all values of ID_Category that do not show up in the SuperCategoryID column (2, 4, and 6), but I'm having trouble writing the SQL.

这相当于找到没有出现在SuperCategoryID列(2,4和6)中的所有ID_Category值,但是我在编写SQL时遇到了麻烦。

I'm using VB6 to query an Access 2000 database.

我正在使用VB6来查询Access 2000数据库。

Any help is appreciated. Thanks!

任何帮助表示赞赏。谢谢!

EDIT: I voted up everyone's answer that gave me something that worked. I accepted the answer that I felt was the most instructive. Thanks again for your help!

编辑:我投了大家的答案给了我一些有用的东西。我接受了我觉得最有启发性的答案。再次感谢你的帮助!

6 个解决方案

#1

Mike Pone's answer works, because he joins the "Categories" table with the "SuperCategories" table as a "LEFT OUTER JOIN" - this will take all entries from "Categories" and add columns from "SuperCategories" to those where the link exists - where it does not exist (e.g. where there is no entry in "SuperCategories"), you'll get NULLs for the SuperCategories columns - and that's exactly what Mike's query then checks for.

Mike Pone的回答是有效的,因为他将“类别”表与“超级类别”表连接为“左外部联接” - 这将从“类别”中获取所有条目,并将“SuperCategories”中的列添加到链接所在的列 - 在它不存在的地方(例如,“SuperCategories”中没有条目),你会得到SuperCategories列的NULL - 这正是Mike的查询然后检查的。

If you would write the query like so:

如果你这样编写查询:

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

you would get something like this:

你会得到这样的东西:

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

So this basically gives you your answer - all the rows where the ID_Super on the LEFT OUTER JOIN is NULL are those who don't have any entries in the SuperCategories table. All clear? :-)

因此,这基本上为您提供了答案 - LEFT OUTER JOIN上的ID_Super为NULL的所有行都是SuperCategories表中没有任何条目的行。全清? :-)

Marc

#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

I'll also make the suggestion that your naming standards could probably use some work. They seem all over the place and difficult to work with.

我还会建议您的命名标准可能会使用一些工作。他们似乎到处都是,很难合作。

#3

include only those categories that don't are not super cateogories. A simple outer join

仅包括那些不是超级类别的类别。一个简单的外连接

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

#4

Not sure if the syntax will work for Access, but something like this would work:

不确定语法是否适用于Access,但这样的方法可行:

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

#5

I always take the outer join approach as marc_s suggests. There is a lot of power when using OUTER JOINS. Often times I'll have to do a FULL OUTER JOIN to check data on both sides of the query.

正如marc_s建议的那样,我总是采用外连接方法。使用OUTER JOINS时有很多功能。通常我必须进行FULL OUTER JOIN来检查查询两侧的数据。

You should also look at the ISNULL function, if you are doing a query where data can be in either table A or table B then I will use the ISNULL function to return a value from either column.

您还应该查看ISNULL函数,如果您正在进行查询,其中数据可以在表A或表B中,那么我将使用ISNULL函数从任一列返回值。

Here's an example

这是一个例子


 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

#6

I have two tables interface_category and interface_subcategory.

我有两个表interface_category和interface_subcategory。

Interface_subcategory contains SubcategoryID, CategoryID, Name(SubcategoryName)

Interface_subcategory包含SubcategoryID,CategoryID,Name(SubcategoryName)

Interface_category contains CategoryID, Name(CategoryName)

Interface_category包含CategoryID,Name(CategoryName)

Now I want output CategoryID and Name(Subcategory name)

现在我想要输出CategoryID和Name(子类别名称)

Query I written is 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

#1