How to get particular result set from table

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

How to get particular result set from table

Author

Topic

saranchandru
Starting Member

4 Posts

Posted - 2011-05-13 : 03:32:03

Dear Members,
The table structure as follows

NAME gcode rcode
name1 CO 45
name1 CO 16
name1 PR 1
name1 PR 2
name2 CO 45
name2 CO 16
name2 PR 1
name3 CO 45
name3 CO 16

select distinct [NAME]
from table1 t1 where
( (t1.gcode='CO' and t1.rcode='45') OR (t1.gcode='CO' and t1.rcode='16'))

the above query will give the result
name1
name2
name3

bcos all names are having CO-45, CO-16

If you see name 3 has exactly 2 rows in table2 with CO-45 and CO-16
How to write a SQL to get the result as ONLY name3

that means I want the names which are having exaclty CO-45 and CO-16

Thanks & Regards
Saravanan Chandru

mrashish77
Starting Member

4 Posts

Posted - 2011-05-13 : 03:57:37

you can add one condition as

select distinct [NAME]
from table1 t1 where
( (t1.gcode='CO' and t1.rcode='45') OR (t1.gcode='CO' and t1.rcode='16'))

and NAME Not in
(
select distinct [NAME]
from table1 t2
where (t2.gcode<>'CO' and t2.rcode<>'45') OR (t2.gcode<>'CO' and t2.rcode<>'16')
)

mrashish77
Starting Member

4 Posts

Posted - 2011-05-13 : 03:59:43

you can even igonre your where condition

saranchandru
Starting Member

4 Posts

Posted - 2011-05-13 : 04:21:55

Thanks for your immediate reply. Problem is NOT IN and <> is taking too long. Is there any way write the query?

Thanks & Regards
Saravanan Chandru

mrashish77
Starting Member

4 Posts

Posted - 2011-05-13 : 05:39:51

Also check below query

select [NAME],Flag
from
(
select [NAME],
sum(case when (t1.gcode='CO' and t1.rcode='45') OR (t1.gcode='CO' and t1.rcode='16') then 100 else 1 end) as Flag
from table1 t1
group by Name
) a
where Flag % 100 = 0

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-13 : 06:05:33

[code]-- From Peso I have learned this:
declare @Sample table(Name varchar(255),GCode varchar(255),RCode varchar(255))
insert @Sample(Name, GCode, RCode)
select 'name1', 'CO', '45' union all
select 'name1', 'CO', '16' union all
select 'name1', 'PR', '1' union all
select 'name1', 'PR', '2' union all
select 'name2', 'CO', '45' union all
select 'name2', 'CO', '16' union all
select 'name2', 'PR', '1' union all
select 'name3', 'CO', '45' union all
select 'name3', 'CO', '16'

select [Name]
from @Sample t1
--where
--( (t1.gcode='CO' and t1.rcode='45') OR (t1.gcode='CO' and t1.rcode='16'))
group by [Name]
having min(case when (t1.gcode='CO' and t1.rcode='45') OR (t1.gcode='CO' and t1.rcode='16')
then 1 else 0 end)=1

[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

saranchandru
Starting Member

4 Posts

Posted - 2011-05-13 : 07:59:14

Great Idea's. Thanks. Its working fine for the listed case. But If I add one more row

name4, CO, 45

That is also coming in the result set. At that time also I expect the result set to be "name3" only. Can you guide me please. Thanks in advance.

Thanks & Regards
Saravanan Chandru

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-13 : 08:11:25

Should be this:

select [Name]
from @Sample t1 
group by [Name]
having sum(case when (t1.gcode='CO' and t1.rcode='45') then 1
                when (t1.gcode='CO' and t1.rcode='16') then 1
                else -1 end)=2

No, you're never too old to Yak'n'Roll if you're too young to die.

saranchandru
Starting Member

4 Posts

Posted - 2011-05-13 : 08:27:41

Thank you so much. Your query is working fine. Thanks you friends.

Thanks & Regards
Saravanan Chandru

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources