Styjun

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

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

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;

Result

| 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 Fiddle example

;with installed as
( select PACK from software where TAGNUM in ( select tagnum from PC where comp in ( select comp from computer where MFRNAME = 'HP')))
select * from Package p left join installed i on p.pack = i.pack
where i.pack is null