Compare Column/Row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Compare Column/Row

Author

Topic

Amber99
Starting Member

11 Posts

Posted - 2009-04-05 : 12:00:31

Hi!

I dont know what I am missing from my code?

QUESTION: Find out the makers that sale PCs but not laptops.

MY CODE:
Select DISTINCT Product.maker AS Maker from Product
where Product.type = 'PC';

Answer:

Maker
A
B
E

BUT the correct answer is Maker E only because the other Maker also produce Laptop and Printer along with PC's and they only want the Maker that makes PC only.

I dont know what to do!? I am learning SQL and its getting difficult now... please guide me

Thank you

Amber

The database scheme consists of four tables:
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)

singularity
Posting Yak Master

153 Posts

Posted - 2009-04-05 : 13:05:36

Select DISTINCT Product.maker AS Maker from Product
where Product.type = 'PC'
and product.maker not in (select DISTINCT Product.maker from Product
where Product.type = 'Laptop')

Amber99
Starting Member

11 Posts

Posted - 2009-04-05 : 14:17:01

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-06 : 05:27:07

See if this also works

Select maker AS Maker from Product
group by maker
Having sum(case when type='PC' then 1 else 2 end)=1

Madhivanan

Failing to plan is Planning to fail

Amber99
Starting Member

11 Posts

Posted - 2009-04-06 : 20:33:58

your version is close i think. but does not produce an answer... i dont know what the numbers are in your code so i wasnt able to modify it...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 10:07:50

quote:
Originally posted by Amber99

your version is close i think. but does not produce an answer... i dont know what the numbers are in your code so i wasnt able to modify it...

they are some arbitrary numbers used to check the presence of pc .

you can use like this also


Select maker AS Maker from Product
group by maker
Having sum(case when type='PC' then 1 else 0 end)>0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources