Tell me how to get output from this tables

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

SQL Server 2000 Forums

Transact-SQL (2000)

Tell me how to get output from this tables

Author

Topic

raju2647
Starting Member

22 Posts

Posted - 2004-09-01 : 00:14:34

hi frds
would anyone please tell me out to get output from two tables listed below.the contents of the table are given below.
name location id
step1 london 12
step1 france 12
step2 newyork 14
step3 france 15

table 2

name location id
step1 london 102
step1 norway 102
step2 newyork 104
step3 france 105

i want to get the output as
step2& step 3

please help

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-01 : 00:24:50

what do you mean by step2& step3?

mk_garg

raju2647
Starting Member

22 Posts

Posted - 2004-09-01 : 01:06:41

quote:
Originally posted by mk_garg20

what do you mean by step2& step3?

mk_garg

hai

take the table 1 as new and the other as old
name==system name
so system can have any location with same id or different id.if both tables contain a system with same location & count of location for an id same as count of location for id in the other table then it should take the system name and id from new table

waiting 4 ur reply

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-01 : 01:43:32

you mean final result will be

step1 london 12
step2 newyork 14
step3 france 15

right?

mk_garg

raju2647
Starting Member

22 Posts

Posted - 2004-09-01 : 01:52:03

quote:
Originally posted by mk_garg20

you mean final result will be

step1 london 12
step2 newyork 14
step3 france 15

right?

mk_garg

NO london wont come as the count of id from both sides for step1 would be same but the locations are different so it will not come.
.only step 2 ,step 3 will come
waiting for reply

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-01 : 01:52:42

check this out

create table #table1(name varchar(10), location varchar(20), id int)
insert into #table1
select 'step1','london',12
union all
select 'step1','france',12
union all
select 'step2','newyork',14
union all
select 'step3','france',15

create table #table2(name varchar(10), location varchar(20), id int)
insert into #table2

select 'step1','london',102
union all
select 'step1','norway',102
union all
select 'step2','newyork',104
union all
select 'step3','france',105

select #table1.name,#table1.location,#table1.id
from #table1 inner join #table2 on (#table1.name=#table2.name and #table1.location=#table2.location)

drop table #table1
drop table #table2

mk_garg

raju2647
Starting Member

22 Posts

Posted - 2004-09-01 : 02:28:51

quote:
Originally posted by mk_garg20

check this out

create table #table1(name varchar(10), location varchar(20), id int)
insert into #table1
select 'step1','london',12
union all
select 'step1','france',12
union all
select 'step2','newyork',14
union all
select 'step3','france',15

create table #table2(name varchar(10), location varchar(20), id int)
insert into #table2

select 'step1','london',102
union all
select 'step1','norway',102
union all
select 'step2','newyork',104
union all
select 'step3','france',105

select #table1.name,#table1.location,#table1.id
from #table1 inner join #table2 on (#table1.name=#table2.name and #table1.location=#table2.location)

drop table #table1
drop table #table2

mk_garg

hi
but london will still come the problem is how to remove london from the list.should i use cursors for doing that please help

waiting for ur reply
regards raju

raju2647
Starting Member

22 Posts

Posted - 2004-09-01 : 02:36:32

from the table u can get the o/p for the table variable and i have written a script with cursors .i am not much familiar with cursors .

------
declare @new_count table
(sys_name varchar(50),sys_loc varchar(50),sys_id int, sys_count int)
insert @new_count select name1,location,id,count(location) from @new group by name1,id,location

select * from @new_count

declare @old_count table
(sys_name varchar(50),sys_loc varchar(50),sys_id int, sys_count int)
insert @old_count select name1,location,id,count(location) from @old group by name1,id,location

select * from @old_count

declare @mig table
(name varchar(50),id int)

declare @row1 varchar(100),@row2 varchar(100),@row3 int,@row4 int,@acc1 varchar(100),@acc2 varchar(100),@o_loc varchar(100),@o_cnt int
declare @cnt int
set @cnt=0
declare new_cursor CURSOR
optimistic
for
select sys_name,sys_loc,sys_id,sys_count from @new_count
open new_cursor
fetch next from new_cursor into @row1,@row2,@row3,@row4
while @@fetch_status=0
begin
declare old_cursor CURSOR
optimistic
for
select sys_loc,sys_count from @old_count where sys_name=@row1
open old_cursor
fetch next from old_cursor into @o_loc,@o_cnt
while @@fetch_status=0
begin

if @o_cnt=@row4
begin
while (@row2) is not null
begin
if @o_loc=@row2
begin
set @cnt=@cnt+1
print @cnt
if @cnt=@o_cnt
begin
insert into @Mig values(@row1,@row3)
end
end
fetch next from new_cursor into @row2
end
end
else
begin
break
end
fetch next from old_cursor into @o_loc

end
close old_cursor
deallocate old_cursor
fetch next from new_cursor into @row3
end
close new_cursor
deallocate new_cursor

------

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-01 : 02:39:00

what are you doing here?
If you are creating new tables then you should define some Primary keys in the tables.

mk_garg

raju2647
Starting Member

22 Posts

Posted - 2004-09-01 : 03:05:40

hi
this are two tables from different DB.during migration data in new was corrupted ie unwanted data.so i am trying to get the orginal migrated data for updation.this query is only for executing once.ie i am using table variable.could u please c weather the script is correct

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-01 : 18:18:06

I have not worked alot on cursors. I will see if can help you. otherwise someone else will help you here.

mk_garg

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-01 : 18:57:28

I am trying to wrap my head around what this confusing thread is about...

So you are saying that you want to query on these two tables but not include london...

Select *
From table1 as A
Inner Join table2 as B
On A.name = B.name
and A.location = B.location
Where location <> 'London'

If this isn't it, try posting some sample data as it would be before your query ... and the desired result. Then I am sure we can straighten this out. No need for cursors that I see yet.

Corey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-02 : 09:54:39

quote:

I am trying to wrap my head around what this confusing thread is about...

LOL -- the most frustrating thing about these forums is we spend more time trying to figure out the QUESTION as opposed to the ANSWER.

- Jeff

mr_mist
Grunnio

1870 Posts

Posted - 2004-09-02 : 10:24:03

I think he wants all the steps where the locations for those steps are the same. Maybe

-------
Moo. :)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-02 : 11:04:28

In other words: determine which steps have only one location.

Select *
from table1 as t1
Inner Join (Select name, location From table1 Group By name, location having count(*)=1) as A
on t1.name = a.name
and t1.location = a.location

but i still don't get what this has to do with table2... [sigh]

I give up until the question is phrased clearly...

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-02 : 11:06:55

well after turning my ESP server on

i'm thinking this is what he wants... based on mr_mist's previous solution:


select 	t1.name, t1.location, t1.id
from #table1 t1 
	inner join  #table2 t2 on (t1.name=t2.name and t1.location=t2.location)
where  t1.name not in
(select distinct t3.name
from #table1 t3 
	inner join  #table2 t4 on (t3.name=t4.name)
where t3.location <> t4.location)

Go with the flow & have fun! Else fight the flow :)

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-02 : 18:21:28

After looking your responses, i am sure he got answer to his question.

mk_garg

raju2647
Starting Member

22 Posts

Posted - 2004-09-10 : 03:15:36

thanks for ur help i got the o/p without using cursors

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources