0

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

enter image description here

Improve this question
5
  • 1
    this looks like some sort of homework. Before we attempt to help you with the query, Is there any particular method of query that you must use or cannot use ? Commented Mar 28, 2019 at 4:24
  • I have been told to only use sub-queries. Commented Mar 28, 2019 at 4:37
  • Any query that answers a question "where something doesnt exist" is generally best answered with a NOT EXISTS(subquery) pattern. Makes it really easy to understand and generally efficient to run. I would research that. Commented Mar 28, 2019 at 4:37
  • 2
    mysql or sql-server? They aren't the same thing. Commented Mar 28, 2019 at 5:06
  • This is for Microsoft SQL server management studio 17 Commented Mar 28, 2019 at 12:53

2 Answers 2

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0 
;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
Improve this answer

3 Comments

Thanks for the prompt reply but I am still receiving a result with PACKNAME MANTA in the list.
It must be in the list then. have you checked?
Yes. Manta is installed on the HP computer associated with the PACK DB22 but the question is phrased in a way that it is looking for all packages that are not installed on any HP computers

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.