Help me to select the records?

Question

Post reply

Help me to select the records?

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Gullimeel Hall of Fame Points: 3871 More actions · Answer 1

Try this as well.. This is based on Lynn's DML

select

td.ID,

td.Unit,

td.SysCC

from

#TestData td

left join (select ID from #TestData where SysCC = 8) tdex

on td.ID = tdex.ID

and td.SysCC = 9

where

tdex.ID is null;

GulliMeel

Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]

Charmer SSChampion Points: 14903 More actions · Answer 2

Lynn Pettis (6/29/2012)
Now, did you take the code that I posted based on David's post to see if that did what you wanted? If so, did it based on your initial post? If so, have you tried to modify it to work with your new requirements?
Here is the code again:
create table #TestData (
ID int,
Unit int,
SysCC int
);
go
insert into #TestData(ID, Unit, SysCC)
values(100,38142,1),
(100,65982,6),
(100,46413,8),
(100,64685,9),
(101,46551,3),
(101,64651,8),
(102,46465,2),
(102,56465,9);
go
select
td.ID,
td.Unit,
td.SysCC
from
#TestData td
where
td.SysCC <> 9
or not exists(select
1
from
#TestData td1
where
td1.ID = td.ID
and td1.SysCC = 8);
go
drop table #TestData;
go

Yes Lynn, this code will meet my requirement....but when i try this code with my code, i am not able to get the output that i am looking for..

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 3

Gullimeel (6/29/2012)
Try this as well.. This is based on Lynn's DML
select
td.ID,
td.Unit,
td.SysCC
from
#TestData td
left join (select ID from #TestData where SysCC = 8) tdex
on td.ID = tdex.ID
and td.SysCC = 9
where
tdex.ID is null;

This one really worked out :-)...Thanks a lot to Gullimeel and Thank you so much to Lynn....

Thanks to all

Thanks,
Charmer

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Charmer (6/29/2012)
Lynn Pettis (6/29/2012)
Now, did you take the code that I posted based on David's post to see if that did what you wanted? If so, did it based on your initial post? If so, have you tried to modify it to work with your new requirements?
Here is the code again:
create table #TestData (
ID int,
Unit int,
SysCC int
);
go
insert into #TestData(ID, Unit, SysCC)
values(100,38142,1),
(100,65982,6),
(100,46413,8),
(100,64685,9),
(101,46551,3),
(101,64651,8),
(102,46465,2),
(102,56465,9);
go
select
td.ID,
td.Unit,
td.SysCC
from
#TestData td
where
td.SysCC <> 9
or not exists(select
1
from
#TestData td1
where
td1.ID = td.ID
and td1.SysCC = 8);
go
drop table #TestData;
go
Yes Lynn, this code will meet my requirement....but when i try this code with my code, i am not able to get the output that i am looking for..

Probably because it needs to be modified to work with multiple tables. It looks like you got it to work using slightly different code, so that is good.

Charmer SSChampion Points: 14903 More actions · Answer 5

Yes, Lynn....i did exactly like you said...

Thank you very much Lynn....You have been a great asset to this SQL community...we are proud of it..:-)

Thanks,
Charmer