| Author |
 Topic  |
|
|
ashley.sql
Constraint Violating Yak Guru
USA
299 Posts |
 Posted - 08/15/2007 : 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)
Singapore
16769 Posts |
Posted - 08/15/2007 : 09:48:12
|
Sorry, Did i missed something or isn't your query is giving the result that you want ?
KH
Time is always against us
|
 |
|
|
ashley.sql
Constraint Violating Yak Guru
USA
299 Posts |
Posted - 08/15/2007 : 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)
Singapore
16769 Posts |
Posted - 08/15/2007 : 09:54:44
|
you can use coalesce(t1.ID, t2.ID) as ID
KH
Time is always against us
|
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
|
|
ashley.sql
Constraint Violating Yak Guru
USA
299 Posts |
Posted - 08/15/2007 : 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
USA
7423 Posts |
Posted - 08/15/2007 : 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
|
Edited by - jsmith8858 on 08/15/2007 14:04:10 |
|
|
|
ashley.sql
Constraint Violating Yak Guru
USA
299 Posts |
Posted - 08/15/2007 : 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
USA
7423 Posts |
Posted - 08/15/2007 : 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
|
Edited by - jsmith8858 on 08/15/2007 14:45:28 |
|
|
|
ashley.sql
Constraint Violating Yak Guru
USA
299 Posts |
Posted - 08/15/2007 : 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
USA
299 Posts |
Posted - 08/15/2007 : 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
USA
7423 Posts |
Posted - 08/15/2007 : 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
USA
7423 Posts |
Posted - 08/15/2007 : 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
|
Edited by - jsmith8858 on 08/15/2007 14:57:54 |
|
|
|
ashley.sql
Constraint Violating Yak Guru
USA
299 Posts |
Posted - 08/15/2007 : 16:27:03
|
union will not give me the sums properly.
I tested it for my data. thanks
Ashley Rhodes |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 08/15/2007 : 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
|
Edited by - jsmith8858 on 08/15/2007 19:37:20 |
|
|
|
ashley.sql
Constraint Violating Yak Guru
USA
299 Posts |
Posted - 08/16/2007 : 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 |
|
|
| |
Topic |
|
|
|
query about joining 2 derived tables
