Joining two results

I have these results:
ID Worked
1 25
2 30
5 20
8 32
9 27
10 26
ID Spent
1 35
2 24
3 22
4 50
5 30
6 28
Required result:
ID Worked Spent
1 25 35
2 30 24
3 0 22
4 0 50
5 20 30
6 0 28
8 32 0
9 27 0
10 26 0
I think I can make it but it will be complicated, am I missing
something or isn't there an easy way to do this?
Thanks in advance,
Stijn Verrept.Stijn,
Try:
SELECT T1.ID, ISNULL(WORKED,0)AS 'WORKED', ISNULL(SPENT,0)AS 'SPENT'
FROM T1 LEFT JOIN T2
ON T1.ID = T2.ID
UNION
SELECT T2.ID, ISNULL(WORKED,0), ISNULL(SPENT,0)
FROM T2 LEFT JOIN T1
ON T2.ID = T1.ID
HTH
Jerry
"Stijn Verrept" <stjin@.entrysoft.com> wrote in message
news:8qOdnfs8vuCJGMjenZ2dnUVZ8qCdnZ2d@.sc
arlet.biz...
>I have these results:
> ID Worked
> 1 25
> 2 30
> 5 20
> 8 32
> 9 27
> 10 26
> ID Spent
> 1 35
> 2 24
> 3 22
> 4 50
> 5 30
> 6 28
>
> Required result:
> ID Worked Spent
> 1 25 35
> 2 30 24
> 3 0 22
> 4 0 50
> 5 20 30
> 6 0 28
> 8 32 0
> 9 27 0
> 10 26 0
> I think I can make it but it will be complicated, am I missing
> something or isn't there an easy way to do this?
>
> Thanks in advance,
> Stijn Verrept.|||Stijn,
Depending on the structure of your data storage there are a number of differ
ent
way to go about this. One possibility is to try:
1. Assume the data is stored in 2 tables (workedTable and spentTable).
2. Create a list of ids by unioning a distinct list of id's from both tables
.
3. Left join this list with workedTable.
4. Left join this with spentTable.
5. Dont forget to isnull the values to return 0's instead of nulls
Code would look something like:
select idTable.ID, isNull(wTable.worked,0), isNull(sTable.spent,0) from
(select distinct id from workedTable union select dictinct if from spentTabl
e)
idTable
left join workedTable wTable on idTable.id=wTable.id
left join spentTable sTable on idTable.id=sTable.id
I'm writing this code off the top of my head, so it may not be exact, byt
hopefully it should get across the idea.
Let me know if you have any success.
Regards,
Andrew McNerlin

> I have these results:
> ID Worked
> 1 25
> 2 30
> 5 20
> 8 32
> 9 27
> 10 26
> ID Spent
> 1 35
> 2 24
> 3 22
> 4 50
> 5 30
> 6 28
> Required result:
> ID Worked Spent
> 1 25 35
> 2 30 24
> 3 0 22
> 4 0 50
> 5 20 30
> 6 0 28
> 8 32 0
> 9 27 0
> 10 26 0
> I think I can make it but it will be complicated, am I missing
> something or isn't there an easy way to do this?
> Thanks in advance,
> Stijn Verrept.
>|||Jerry Spivey wrote:

> Stijn,
> Try:
> SELECT T1.ID, ISNULL(WORKED,0)AS 'WORKED', ISNULL(SPENT,0)AS 'SPENT'
> FROM T1 LEFT JOIN T2
> ON T1.ID = T2.ID
> UNION
> SELECT T2.ID, ISNULL(WORKED,0), ISNULL(SPENT,0)
> FROM T2 LEFT JOIN T1
> ON T2.ID = T1.ID
That indeed works out nicely and isn't too complicated! Thanks.|||You could also do it like so:
SELECT COALESCE(T1.ID, T2.ID) as ID,
COALESCE(WORKED,0)AS 'WORKED',
COALESCE(SPENT,0) AS 'SPENT'
FROM T1 FULL OUTER JOIN T2
ON T1.ID = T2.ID
It may perform better if you have a large number of rows.|||Stu wrote:

> You could also do it like so:
> SELECT COALESCE(T1.ID, T2.ID) as ID,
> COALESCE(WORKED,0)AS 'WORKED',
> COALESCE(SPENT,0) AS 'SPENT'
> FROM T1 FULL OUTER JOIN T2
> ON T1.ID = T2.ID
> It may perform better if you have a large number of rows.
Thanks!sql