query about joining 2 derived 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 2005 Forums

Transact-SQL (2005)

query about joining 2 derived tables

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-15 : 09:35:36

I have 2 dervied tables

----THIS HAS IDTYPE = C

create table #t1(ID int, IDType char(1), Cash money)
insert into #t1 values (1234, 'C', 2000)
insert into #t1 values (4568, 'C', 3000)
insert into #t1 values (7879, 'C', 5000)

----THIS HAS IDTYPE = N

create table #t2(ID int, IDType char(1), Cash money)
insert into #t2 values (1234, 'N', 1000)
insert into #t2 values (2222, 'N', 8000)
insert into #t2 values (1111, 'N', 7000)

Now I want to join these 2 tables and get this result

ID, IDTYPE_C, CASH_C, IDTYPE_N, CASH_N, SUM(CASH_C+CASH_N)
1234,C,2000, N, 1000, 3000
4568,C,3000, NULL, NULL, NULL, 3000
7879,C, 5000, NULL, NULL, NULL, 5000
NULL, NULL, NULL,2222,N, 8000, 8000
NULL, NULL, NULL,1111,N, 7000, 7000

THIS IS MY QUERY SO FAR

select t1.ID ID_C, t1.IDtype IDTYPE_C, t1.cash CASH_C,t2.ID ID_N, t2.idtype IDTYPE_N, t2.cash CASH_N
, isnull(t1.cash, 0)+isnull(t2.cash,0) as SUM
from #t1 t1 full outer join #t2 t2 on t1.id = t2.id

order by t1.id desc

I WANT THE DATA IN COLUMNS ID_C and ID_N to appear in one column like mentioned above in my result.

Is there a way to do this.

Ashley Rhodes

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-15 : 09:48:12

Sorry, Did i missed something or isn't your query is giving the result that you want ?

KH
[spoiler]Time is always against us[/spoiler]

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-15 : 09:52:48

if you see the result of my query

I have 2 columns

ID_C and ID_N

I want them as only column in the result say column name as ID.

Ashley Rhodes

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-15 : 09:54:44

you can use coalesce(t1.ID, t2.ID) as ID

KH
[spoiler]Time is always against us[/spoiler]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-15 : 12:31:23

It is my advice to avoid full outer joins, see:

http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx

for reasons why and better ways to write your SQL.

- Jeff
http://weblogs.sqlteam.com/JeffS

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-15 : 13:32:27

what other join will give me the data i need in the results.
FULL Outer join seems to work fine in this case. but yes there might be some scenarios in which it might not work. can u suggest another way

Ashley Rhodes

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-15 : 14:03:10

As the article shows, just use a UNION. you might want to also clarify the results you want returned, it is not clear what you are asking for. What exactly do you want returned ??

- Jeff
http://weblogs.sqlteam.com/JeffS

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-15 : 14:37:43

whatever is the result of the query. i cannot use union as the 2 columns are suppose to have different names and then i need the sum of those 2 columns in 3rd column

Ashley Rhodes

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-15 : 14:44:22

>> whatever is the result of the query.

?? I don't understand what you are saying. Again, how about some sample results that reflect what you actually want??

>>i cannot use union as the 2 columns are suppose to have different names and then i need the sum of those 2 columns in 3rd column

Why do you think you cannot use a UNION ? Also, is your data all in one table originally and you are breaking it out into temp tables at some point in the process, or is it really in two tables to begin with? If it is all in one table, it's even easier, you don't need a GROUP BY at all.

Let's eliminate the guesswork. Just show EXACTLY what you want returned, not something that you are getting back and then a vague description of why it's wrong.

- Jeff
http://weblogs.sqlteam.com/JeffS

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-15 : 14:49:03

I have already posted that in my initial post

Now I want to join these 2 tables and get this result

ID, IDTYPE_C, CASH_C, IDTYPE_N, CASH_N, SUM(CASH_C+CASH_N)
1234,C,2000, N, 1000, 3000
4568,C,3000, NULL, NULL, NULL, 3000
7879,C, 5000, NULL, NULL, NULL, 5000
NULL, NULL, NULL,2222,N, 8000, 8000
NULL, NULL, NULL,1111,N, 7000, 7000

Ashley Rhodes

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-15 : 14:49:55

so far i am getting the result i want so i am not bothered. the data is simple.
if the scenarios change in future and i get bugs i will worry about it.
but so far so good.

Ashley Rhodes

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-15 : 14:53:05

Then what did you mean by this?

quote:

if you see the result of my query

I have 2 columns

ID_C and ID_N

I want them as only column in the result say column name as ID.

Which do you want? What you are displaying, or what you are saying? they do not match.

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-15 : 14:56:57

If I had to guess, all you need is a union like this:

select ID, sum(cashC), sum(CashN), sum(Total)
from
(
select ID, cash as CashC, 0 as CashN, cash as Total
from #t1
union all
select ID, 0 as cashC, Cash as CashN, cash as Total
from #t2
) x
group by ID

If the data is really all in one table and not in two tables, it is even easier.

- Jeff
http://weblogs.sqlteam.com/JeffS

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-15 : 16:27:03

union will not give me the sums properly.

I tested it for my data. thanks

Ashley Rhodes

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-15 : 16:40:00

quote:
Originally posted by ashley.sql

union will not give me the sums properly.

I tested it for my data. thanks

Ashley Rhodes

It should SUM fine if you wrote it right; it is not only more efficient but also more accurate than a FULL OUTER JOIN.

For example, if you do a full outer join with:

ID, Type, value
abc, C, 10

and

ID,value
abc, N, 20
abc, N, 30

you will get a SUM() of 20 for Type C, even though it should be 10, because of duplication caused by JOINing that single row with two matching rows in the other table. A UNION of the two tables instead will not have this problem. Test it yourself on your sample data if you are not sure.

Again, if you had simply given good sample data and accurate, expected results this would save a lot of time and you'd be sure to have the best possible solution. Right now it is still all guesswork: what data you really have, what tables it is in, what primary keys exist (if any), and what you really want ....

- Jeff
http://weblogs.sqlteam.com/JeffS

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-16 : 13:53:53

we are still in process of getting the data.
so its just initial testing
i am not even sure when we will test and be required to create the final report

Ashley Rhodes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources