不与子查询一起使用

本文介绍了不与子查询一起使用的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我被要求在我的 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 cp = pcp 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 小提琴示例