retrive from table usign select statement

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008

SQL

create table markdetails(id int, mark int)
Insert into markdetails values(1,50),(1,60),(1,70),(2,50),(2,60),(3,50),(3,50),(4,50),(5,50),(5,60)

I need output

id mark
3 50
4 50

ex: what are the id have only 50 that id dont have any other values.
(same value is no problem).
id 3 have two 50. display one its enough.

Posted 14-Mar-13 19:57pm

jmohanraj2008

Updated 14-Mar-13 20:10pm

Karthik Harve

v3

Add a Solution

Comments

Prasad Khandekar 15-Mar-13 2:10am

Hello Mohan,

Do you mean to say that select only those id's which do not have values other than 50?

Regards,

Shubham Choudhary 15-Mar-13 2:18am

why are you not using DISTINCT keyword
like this
SELECT DISTINCT column_name(s)
FROM table_name

Sergey Alexandrovich Kryukov 24-May-13 2:45am

Please stop posting non-answers as "solution". It can give you abuse reports which eventually may lead to cancellation of your CodeProject membership.
Comment on any posts, reply to available comments, or use "Improve question" (above).
Also, keep in mind that members only get notifications on the post sent in reply to there posts.
—SA

3 solutions

Solution 2

thanks dude

Insert into markdetails values(1,50),(1,60),(1,70),(2,50),(2,60),(3,50),(3,50),(4,50),(5,50),(5,60)

i need out put like this

id mark
2 50
2 60
3 50
4 50
5 50
5 60

Posted 14-Mar-13 20:34pm

jmohanraj2008

Comments

Shubham Choudhary 15-Mar-13 3:30am

hi!!! don't make your comment and question like this!!! delete it and write it on comment box

Kuthuparakkal 15-Mar-13 3:31am

not an answer, use separate thread to initiate new questions!

Solution 3

SQL

SELECT DISTINCT ID,MARK From markdetails WHERE Mark in(50) AND ID NOT IN
( SELECT ID FROM markdetails WHERE Mark not in(50))

Posted 14-Mar-13 23:34pm

jmohanraj2008

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)

Kuthuparakkal · Accepted Answer · 2013-03-14T20:11:00

Solution 1

SQL

select p1.id, p1.mark
from
(
select distinct mark, id from markdetails
where mark=50
)as p1
left outer join
(
select distinct id from markdetails
where mark<>50
)as p2
on p1.id= p2.id
where p2.id is null

Posted 14-Mar-13 20:11pm

Kuthuparakkal

Comments

jmohanraj2008 15-Mar-13 2:21am

thanks yar. now i need both mark 50 and 60 alone in single id

Kuthuparakkal 15-Mar-13 2:25am

SELECT ID, SUBSTRING(MarkConc,0,LEN(MarkConc)) AS Mark FROM
(
SELECT DISTINCT ID,
(
SELECT convert(varchar(20),mark) + ',' AS [text()] FROM
DBO.markdetails AS TBL_A WHERE TBL_A.ID=TBL_B.ID
AND mark IN (50, 60)
FOR XML PATH('')
) AS MarkConc
FROM DBO.markdetails AS TBL_B
)
AS TBL_C

jmohanraj2008 15-Mar-13 2:34am

thanks dude

Insert into markdetails values(1,50),(1,60),(1,70),(2,50),(2,60),(3,50),(3,50),(4,50),(5,50),(5,60)

i need out put like this

id mark
2 50
2 60
3 50
4 50
5 50
5 60