April 22, 2012 at 9:48 am
Hi Friends ,
I have small doubt in sql . suppose
1st table data types id=int (its primarykey) and amount =int
1st tablename = table1 contain data like id , amount
256 , 1200
257 , 1232
258 , 1260
259 , 1400
256 , 5600
259 , 4700
2nd table data type id=int (its foreign key) and dloc =varchar
2nd table name =table2 contain data like id , dloc
2561 ,hyd
2562 ,hyd
2571 ,naguru
2581 , ranu
2591 ,chen
2592 ,chen
How to join both table data
April 22, 2012 at 11:01 am
Hi asranantha,
Please clarify the term "join" you only can join tables when both tables has the same id's using the statement
INNER JOIN Table1.ID ON Table2.ID, but if you need to perform a Union between tables you must to use Union Clause.However when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.
April 22, 2012 at 12:38 pm
Looking at this post, it is a bit more difficult to know what you are trying to accomplish. Looking at the data, however, I was able to make a simple assumption as to how the data might be related.
Please look at the code below and if you have any questions, please ask.
-- Create tables for work in Sandbox
create table dbo.Table1(
id int,
amount int
);
create table dbo.Table2(
id int,
dloc varchar(10)
);
-- Populate tables with sample data
insert into dbo.Table1(id, amount)
select 256, 1200 union all
select 257, 1232 union all
select 258, 1260 union all
select 259, 1500 union all
select 256, 5600 union all
select 259, 4700;
insert into dbo.Table2(id, dloc)
select 2561, 'hyd' union all
select 2562, 'hyd' union all
select 2571, 'naguru' union all
select 2581, 'ranu' union all
select 2591, 'chec' union all
select 2592, 'chen';
-- Some assumptions based on the data. The ID in Table 2 is a combined value with
-- the first three values representing the id in Table 1 and the last part an occurance
-- number. Based on this assumption the following should work.
with T1BaseData as (
select
t1.id,
ROW_NUMBER() over (PARTITION BY id order by (select null)) seq,
t1.amount
from
dbo.Table1 t1
),
T2BaseData as (
select
CAST(left(cast(t2.id as varchar),3) as int) id,
CAST(SUBSTRING(cast(t2.id as varchar),4,DATALENGTH(cast(t2.id as varchar)) - 3) as int) seq,
t2.dloc
from
dbo.Table2 t2
)
select
bd1.id,
bd1.seq,
bd1.amount,
bd2.dloc
from
T1BaseData bd1
inner join T2BaseData bd2
on (bd1.id = bd2.id
and bd1.seq = bd2.seq)
order by
bd1.id,
bd1.seq
;
drop table dbo.Table1;
drop table dbo.Table2;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply