need help with the query

Question

1.00/5 (1 vote)

See more:

Hi
I want to get the maker who sells pc but not laptops the desired output is E,pl refer the attached file for the table structure

VB

product 
maker model type
A     1232  PC
A     1233  PC
A     1276  Printer
A     1298  Laptop
A     1401  Printer
A     1408  Printer
A     1752  Laptop
B     1121  PC
B     1750  Laptop
C     1321  Laptop
D     1288  Printer
D     1433  Printer
E     1260  PC
E     1434  Printer
E     2112  PC
E     2113  PC

Posted 4-Jun-12 19:01pm

Shashwath7

Updated 4-Jun-12 19:02pm

Prasad_Kulkarni

v2

Add a Solution

Comments

Sandeep Mewara 5-Jun-12 11:01am

Homework! No effort.

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rahul Rajat Singh · Answer 1 · 2012-06-04T19:35:00

Solution 5

I think this might be the right solution for you which will give answer as 'E' only

SQL

SELECT     distinct(maker)
FROM         Product
WHERE     type = 'pc'
except

SELECT     distinct(maker)
FROM         product
WHERE     type = 'laptop';

try it and let me know if it works or not.

Posted 4-Jun-12 19:35pm

Rahul Rajat Singh

RDBurmon · Answer 2 · 2012-06-04T19:10:00

Solution 1

SQL

SELECT * FROM Product WHERE type not in ('Laptop')

Hope this helps , If yes then plz accept and vote the answer. Any queries / questions on this are always welcome.

Thanks & Regards
RDBurmon.Sr.Software Engineer

Posted 4-Jun-12 19:10pm

RDBurmon

Comments

Arunprasath Natarajan 5-Jun-12 1:21am

Your query will give the data of Maker(D) who sells printer also.

Mehdi Gholam 5-Jun-12 1:22am

Partially right, see my answer.

Arunprasath Natarajan · Answer 3 · 2012-06-04T19:17:00

Solution 2

Dear Friend,

Hope this will work.

SQL

Select * from My_Table with (Nolock) where type='PC' and maker not in (Select maker from My_Table with (Nolock) where type='Laptop')

Regards,
AP

Posted 4-Jun-12 19:17pm

Arunprasath Natarajan

Updated 4-Jun-12 19:23pm

v2

Mehdi Gholam · Answer 4 · 2012-06-04T19:21:00

Solution 3

You should use:

SQL

select * from ( select * from Product where type <> 'laptop') as t where t.type = 'pc'

Posted 4-Jun-12 19:21pm

Mehdi Gholam

Comments

Rahul Rajat Singh 5-Jun-12 2:13am

I am sorry but i dont understand how is this correct. the result for this query is:

A 1232 PC
A 1233 PC
B 1121 PC
E 1260 PC
E 2112 PC
E 2113 PC

I think he need to find the maker who makes PC and not laptops. Is my understanding wrong? I think he want answer to be 'E' in this particular case.

Prosan · Answer 5 · 2012-06-04T19:22:00

Solution 4

as i understand your query write like this

SQL

select distinct E from product where type='PC'

Posted 4-Jun-12 19:22pm

Prosan

Comments

Rahul Rajat Singh 5-Jun-12 2:08am

The result of this would be:
A
B
E