SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 query about joining 2 derived tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ashley.sql
Constraint Violating Yak Guru

USA
299 Posts

Posted - 08/15/2007 :  09:35:36  Show Profile  Reply with Quote
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)

Singapore
17658 Posts

Posted - 08/15/2007 :  09:48:12  Show Profile  Reply with Quote
Sorry, Did i missed something or isn't your query is giving the result that you want ?


KH
Time is always against us

Go to Top of Page

ashley.sql
Constraint Violating Yak Guru

USA
299 Posts

Posted - 08/15/2007 :  09:52:48  Show Profile  Reply with 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.

Ashley Rhodes
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 08/15/2007 :  09:54:44  Show Profile  Reply with Quote
you can use coalesce(t1.ID, t2.ID) as ID


KH
Time is always against us

Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 08/15/2007 :  12:31:23  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

ashley.sql
Constraint Violating Yak Guru

USA
299 Posts

Posted - 08/15/2007 :  13:32:27  Show Profile  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 08/15/2007 :  14:03:10  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Edited by - jsmith8858 on 08/15/2007 14:04:10
Go to Top of Page

ashley.sql
Constraint Violating Yak Guru

USA
299 Posts

Posted - 08/15/2007 :  14:37:43  Show Profile  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 08/15/2007 :  14:44:22  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
>> 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

Edited by - jsmith8858 on 08/15/2007 14:45:28
Go to Top of Page

ashley.sql
Constraint Violating Yak Guru

USA
299 Posts

Posted - 08/15/2007 :  14:49:03  Show Profile  Reply with Quote
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
Go to Top of Page

ashley.sql
Constraint Violating Yak Guru

USA
299 Posts

Posted - 08/15/2007 :  14:49:55  Show Profile  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 08/15/2007 :  14:53:05  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 08/15/2007 :  14:56:57  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Edited by - jsmith8858 on 08/15/2007 14:57:54
Go to Top of Page

ashley.sql
Constraint Violating Yak Guru

USA
299 Posts

Posted - 08/15/2007 :  16:27:03  Show Profile  Reply with Quote
union will not give me the sums properly.

I tested it for my data. thanks

Ashley Rhodes
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 08/15/2007 :  16:40:00  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Edited by - jsmith8858 on 08/15/2007 19:37:20
Go to Top of Page

ashley.sql
Constraint Violating Yak Guru

USA
299 Posts

Posted - 08/16/2007 :  13:53:53  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000