不与子查询一起使用 [英] using not with sub-queries

问题描述

我被要求在我的 sql server 数据库中显示以下问题的结果

I have been asked to display results in my sql server database for the following question

哪些软件包没有安装在任何 HP 计算机上?我已经尝试了以下方法，但仍然得到 PACKNAME Manta 的结果，但该软件包已安装在 HP 计算机上.我错过了什么?

What software packages are not installed on any HP computers? I have tried the following but I am still getting results for the PACKNAME Manta but that package is installed on an HP computer. What am I missing?

select * from package where PACK in
  ( select PACK from software where TAGNUM in 
   ( select tagnum from PC where comp NOT in
     ( select comp from computer where MFRNAME = 'HP')))

我在下面附上了数据的图片供您参考

I have attached an image of the data for your reference below

推荐答案

您可以将 NOT EXISTS 与将其他表连接在一起并包含过滤条件的相关子查询一起使用:

You can use NOT EXISTS with a correlated subquery that joins the other tables together, and contains your filter condition:

select
pk.pack,
pk.packname,
pk.packv,
pk.packtype,
pk.packcost
from package pk
where not exists (
                    select 1
                    from software s
                    inner join pc on pc.tagnum = s.tagnum
                    inner join computer c on c.comp = pc.comp
                    where s.pack = pk.pack
                    and c.mfrname = 'HP'
                 )
order by pk.pack;

结果

| pack |         packname | packv |        packtype | packcost |
|------|------------------|-------|-----------------|----------|
| AC11 | Quick Accounting |   4.1 |      Accounting |   754.95 |
| AC12 |   Accounting MIS |   4.0 |      Accounting |     2000 |
| AC13 |        Quickbook |  2005 |      Accounting |      300 |
| DB11 |            Manta |   1.5 |        Database |      380 |
| DB13 |       SQL Server |  2005 |        Database |      500 |
| DB14 |           My SQL |  2005 |        Database |      300 |
| SS11 |          Easycal |   5.5 |     Spreadsheet |   225.15 |
| WP04 |       Word Power |     2 | Word Processing |      118 |
| WP07 |        Good Word |   3.2 | Word Processing |       35 |
| WP14 |           GOOGLE |     2 | Word Processing |      118 |

SQL 小提琴示例

这篇关于不与子查询一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！