Display results from 2 tables including empty valu

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 2000 Forums

Transact-SQL (2000)

Display results from 2 tables including empty valu

Author

Topic

vavc1980
Starting Member

3 Posts

Posted - 2008-02-26 : 13:25:18

Hi!
I have a problem with a query, I need to put together into one table the contents of 2 tables, but in the correspondent row for each match, and include the no-matches too.

Table A:
User | IncomingCalls|
---------|-----------------------|
smith | ___10_______ |
jones | ____5_______ |
lee __| ____8 _______ |
harp _| ____2 _______ |

Table B:
User | OutgoingCalls|
---------|-----------------------|
taylor | _____15 _____ |
jones | ______6______ |
lee __| ______1 ______ |

And the result table or view that I want is:

Results Table:
User | IncomingCalls | OutgoingCalls|
---------|---------------------|----------------------|
smith | ____10_____ | ____________|
jones | _____5_____ | ______6_____ |
lee __| _____8_____ | ______1_____ |
harp _| _____2_____ | ____________|
taylor | ___________ | ______15____ |

How can I accomplish this?
I appreciate the help..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-26 : 13:40:54

[code]SELECT ISNULL(a.User,b.User),
a.IncomingCalls,
b.OutgoingCalls
FROM TableA a
FULL OUTER JOIN TableB b
ON b.User=a.User[/code]

vavc1980
Starting Member

3 Posts

Posted - 2008-02-26 : 16:21:09

thanks! I'll try that

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-26 : 16:40:18

Also try

SELECT d.User, SUM(d.IncomingCalls) AS IncomingCalls, SUM(d.OutgoingCalls) AS OutgoingCalls
FROM (
SELECT User, IncomingCalls, 0 AS OutgoingCalls FROM TableA
UNION ALL
SELECT User, 0, OutgoingCalls FROM TableB
) AS d
GROUP BY d.User

E 12°55'05.25"
N 56°04'39.16"

vavc1980
Starting Member

3 Posts

Posted - 2008-02-26 : 17:27:52

I like that last answer a lot (easier for me to follow). I applied it and it works perfect!
Thank you very much for both answers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources