Removing rows from a result

I have a query which returns the following results. What i want to do
is remove any rows where col1 does not appear in col2. e.g. "107". As
well as this i want to remove any rows that are affected by this, e.g.
"109" as once 107 goes, 109 does not appear in col2.
Thanks in advance.
col1 col2
1108
1112
1115
1116
1117
1118
107109
108114
109110
110111
112113
118119
On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
> I have a query which returns the following results. What i want to do
> is remove any rows where col1 does not appear in col2. e.g. "107". As
> well as this i want to remove any rows that are affected by this, e.g.
> "109" as once 107 goes, 109 does not appear in col2.
> Thanks in advance.
> col1 col2
> 1 108
> 1 112
> 1 115
> 1 116
> 1 117
> 1 118
> 107 109
> 108 114
> 109 110
> 110 111
> 112 113
> 118 119
Try something like this
drop table #temp
drop table #temp1
create table #temp (col1 int, col2 int )
insert into #temp values (1, 108 )
insert into #temp values (1, 112 )
insert into #temp values (1, 115 )
insert into #temp values (1, 116 )
insert into #temp values (1, 117 )
insert into #temp values (1 ,118 )
insert into #temp values (107,109 )
insert into #temp values (108,114 )
insert into #temp values (109,110 )
insert into #temp values (110,111 )
insert into #temp values (112,113 )
insert into #temp values (118,119 )
insert into #temp values (118,1 )
create table #temp1 (col1 int, col2 int )
while 1 = 1
begin
truncate table #temp1
insert into #temp1 select * from #temp a where not exists (select 1
from #temp b where a.col1 = b.col2 )
if @.@.rowcount = 0
break
delete #temp from #temp
inner join #temp1 on #temp.col1 = #temp1.col1
end
select * from #temp
|||On Apr 3, 12:59 pm, "M A Srinivas" <masri...@.gmail.com> wrote:
> On Apr 3, 3:45 pm, danielev...@.gmail.com wrote:
>
>
>
> Try something like this
> drop table #temp
> drop table #temp1
> create table #temp (col1 int, col2 int )
> insert into #temp values (1, 108 )
> insert into #temp values (1, 112 )
> insert into #temp values (1, 115 )
> insert into #temp values (1, 116 )
> insert into #temp values (1, 117 )
> insert into #temp values (1 ,118 )
> insert into #temp values (107,109 )
> insert into #temp values (108,114 )
> insert into #temp values (109,110 )
> insert into #temp values (110,111 )
> insert into #temp values (112,113 )
> insert into #temp values (118,119 )
> insert into #temp values (118,1 )
> create table #temp1 (col1 int, col2 int )
> while 1 = 1
> begin
> truncate table #temp1
> insert into #temp1 select * from #temp a where not exists (select 1
> from #temp b where a.col1 = b.col2 )
> if @.@.rowcount = 0
> break
> delete #temp from #temp
> inner join #temp1 on #temp.col1 = #temp1.col1
> end
> select * from #temp
Thanks for this. Is this the only way to achieve this result? I was
hoping to do it in the initial query without using temp tables.
Dan