Is it possible to write a query and get the desired result as below

Question

0.00/5 (No votes)

See more:

--Scenario

table1

id	data1	data2
1	100	150
1	200	250
1	300	350

table2

id	value1	value2
1	1000	1050

i want the result in this way using join. Is it possible ?

id data1 data2 value1 value2
1  100   150    1000   1050
1  200   250    null   null
1  300   350    null   null

-- i m trying this way but not as above result...

SQL

select *
from #temp5 t1
left join #temp6 t2 on t1.id=t2.id
where t1.id=1

Posted 13-Nov-13 22:57pm

anurag19289

Updated 13-Nov-13 22:59pm

v3

Add a Solution

Comments

♥…ЯҠ…♥ 14-Nov-13 5:03am

You sure, first table ID has common ID for all records? it violates the meaning of ID in the sql table even literal meaning of it.

anurag19289 14-Nov-13 5:33am

i have just taken an example as id..

3 solutions

Solution 2

Try this,

SQL

select t1.id,t1.data1,t1.data2,t2.value1,t2.value2 from table2 t2
right join
table1 t1 on t1.id = 1;

Result
--------------

XML

id data1 data2 value1 value2
1  100   150    1000   1050
2  200   250    null   null
3  300   350    null   null

Notice here you can not have same id as you have mentioned.

Posted 13-Nov-13 23:40pm

Thomas ktg

Comments

anurag19289 14-Nov-13 5:47am

--No its not working...

declare @t table(id int, data1 int, data2 int)
declare @b table(id int, value1 int, value2 int)

insert into @t(id, data1, data2)
values(1, 100, 150)
insert into @t(id, data1, data2)
values(1, 200, 250)
insert into @t(id, data1, data2)
values(1, 300, 350)

insert into @b(id, value1, value2)
values(1, 1000, 1050)

select t1.id,t1.data1,t1.data2,t2.value1,t2.value2 from @b t2
right join
@t t1 on t1.id = 1;

Thomas ktg 14-Nov-13 5:50am

insert into @t(id, data1, data2) values(1, 100, 150)
insert into @t(id, data1, data2) values(2, 200, 250)
insert into @t(id, data1, data2) values(3, 300, 350)

this would work in this scenario.

anurag19289 14-Nov-13 6:12am

Thanks but i dont want this result..

And why i have kept id as same becuase...

there are two tables..

table1 [leaveapplied table]

[it contains more than one record for a userid]
userid leaveappliedfromdate leaveappliedtodate
123 12th nov 18th nov
123 25th dec 28th dec

And in another table the same userid

table2[travel table]
userid Travelfromdate Traveltodate
123 29th nov 1st dec

now i wanted to join these two tables ...last 60days and 60 days ahead....wanted to join in such away so to get the desired result as
userid leaveappliedfromdate leaveappliedtodate travelappliedfromdate travelappliedtodate
123 12th nov 18th nov 29th nov 1st dec
123 25th dec 28th dec null null

Thomas ktg 14-Nov-13 6:25am

Thanks. I understand.

Solution 3

SQL

; with c as
               (
               select userid,fromdate,todate, row_number() over (order by userid) r from tbl_LMS_Data with(nolock)
               where userid= @USERID and fromdate  > dateadd(dd,-30,getdate())
               ),e as
               (
               select userid,fromdate as fromdatetas ,todate as todatetas, row_number() over (order by userid) r from tbl_tas_data with(nolock) where userid= @USERID
               and fromdate  > dateadd(dd,-30,getdate())
               )
               select c.userid,c.fromdate,c.todate,fromdatetas,todatetas
               into #temp2
               from c
               full outer join e on c.r = e.r
               order by c.userid desc

two tables are tbl_lms_data [table1] and tbl_tas_data[table2]. So this works perfectly.

Posted 21-Nov-13 9:09am

anurag19289

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dineshshp · Accepted Answer · 2013-11-13T23:16:00

Solution 1

Try This

SQL

declare @t table(id int, data1 int, data2 int)
declare @b table(id int, value1 int, value2 int)

insert into @t(id, data1, data2)
values(1, 100, 150)
insert into @t(id, data1, data2)
values(1, 200, 250)
insert into @t(id, data1, data2)
values(1, 300, 350)

insert into @b(id, value1, value2)
values(1, 1000, 1050)

select x. id, x.data1, x.data2, y.value1, y.value2 from
(
	select ROW_NUMBER() over(partition by t.id order by t.id) Sr, t.id, t.data1, t.data2 from @t t
) x
left join
(
	select ROW_NUMBER() over(partition by b.id order by b.id) Sr, b.id, b.value1, b.value2 from @b b
) y
on x.Sr = y.Sr

Posted 13-Nov-13 23:16pm

Dineshshp

Updated 14-Nov-13 0:33am

v2

Comments

anurag19289 14-Nov-13 5:28am

Let me try and get back to you

Dineshshp 14-Nov-13 5:36am

what's about my answer. is it ok for u; if yes don't forget to give stars.

Thx

anurag19289 14-Nov-13 5:37am

That is cool :) Thanks

anurag19289 19-Nov-13 7:36am

But i have a doubt.... this scenario works...when in table1 we have more data and table2 has less data....... think of a situation where we don know which table has more data...may be table1 or table2 and we want that in single temporary table using the join..what i am doing is as per your suggestion

i am doing as below comment,..

end can you suggest on this ?

anurag19289 19-Nov-13 8:26am

if (@COUNTLMS > @COUNTTAS )
begin

insert #temp2(userid,fromdatelms,todatelms,fromdatetas,todatetas)
select x.userid,x.fromdate,x.todate,y.fromdate,y.todate

from
(
select ROW_NUMBER() over(partition by l.userid order by l.userid) Sr, l.userid, l.fromdate, l.todate from tbl_LMS_Data l WITH(NOLOCK)
where l.FromDate > dateadd(dd,-30,getdate()) and l.FromDate < dateadd(dd,30,getdate())
and l.userid= @userid
) x
left outer join
(
select ROW_NUMBER() over(partition by t.userid order by t.userid) Sr, t.userid, t.fromdate, t.todate from tbl_TAS_Data t WITH(NOLOCK)
where t.FromDate > dateadd(dd,-30,getdate()) and t.FromDate < dateadd(dd,30,getdate())
and t.userid= @userid

) y
on x.Sr = y.Sr

end

else
begin
insert #temp2(userid,fromdatetas,todatetas,fromdatelms,todatelms)
select x.userid,x.fromdate,x.todate,y.fromdate,y.todate

from
(
select ROW_NUMBER() over(partition by t.userid order by t.userid) Sr, t.userid, t.fromdate, t.todate from tbl_TAS_Data t WITH(NOLOCK)
where t.FromDate > dateadd(dd,-30,getdate()) and t.FromDate < dateadd(dd,30,getdate())
and t.userid= @userid
) x
left outer join
(
select ROW_NUMBER() over(partition by l.userid order by l.userid) Sr, l.userid, l.fromdate, l.todate from tbl_LMS_Data l WITH(NOLOCK)
where l.FromDate > dateadd(dd,-30,getdate()) and l.FromDate < dateadd(dd,30,getdate())
and l.userid= @userid

) y
on x.Sr = y.Sr
end