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
 General SQL Server Forums
 New to SQL Server Programming
 Combining Tables

Author  Topic 

BankOfficerHere
Posting Yak Master

124 Posts
Posted - 2008-08-15 : 21:28:53
Hi I have two tables and to combine them into 1.

Table 1
Name EmployeeID Scores
Michael 111 5
Michelle 222 10
Allan 333 6
Gilbert 444 8

Table 2
Name EmployeeID Scores
Michael 111 9
Michelle 222 11
Gilbert 444 7

I want them to be combined as:

Name EmployeeID Scores Scores
Michael 111 5 9
Michelle 222 10 11
Allan 333 6
Gilbert 444 8 7

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2008-08-15 : 21:44:08
have you try using INNER JOIN on it ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts
Posted - 2008-08-15 : 21:44:18
select Table1.Name, Table1.EmployeeID, Table1.Scores, Table2.Scores
from Table1
join Table2 on Table1.EmployeeID=Table2.EmployeeID

elsasoft.org
Go to Top of Page

BankOfficerHere
Posting Yak Master

124 Posts
Posted - 2008-08-15 : 21:53:34
I tried that alredy but the name Allan is not showing on the results.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2008-08-15 : 22:25:40
use LEFT JOIN or FULL OUTER JOIN if you have name in 2nd table but not in 1st table

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

BankOfficerHere
Posting Yak Master

124 Posts
Posted - 2008-08-15 : 23:29:56
okay it's working i have 2 tables. what if I have 4 tables:

Name EmployeeID Scores
Michael 111 5
Michelle 222 10
Allan 333 6
Gilbert 444 8


Name EmployeeID Scores
Michael 111 9
Michelle 222 11
Gilbert 444 7


Name EmployeeID Scores
Michelle 222 11
Gilbert 444 7

Name EmployeeID Scores
Michael 111 5
Michelle 222 10
Allan 333 6
Gilbert 444 8


The results should be:

Name EmployeeID Scores Scores Scores Scores
Michael 111 5 9 5
Michelle 222 10 11 11 10
Allan 333 6 6
Gilbert 444 8 7 7 8
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2008-08-15 : 23:39:57
you can also use FULL OUTER JOIN for that. Another way is to use UNION ALL

select Name, EmployeeID, sum(Scores1), sum(Scores2), sum(Scores3), sum(Scores4)
from
(
select Name, EmployeeID, Scores as Scores1, NULL as Scores2, NULL as Scores3, NULL as Scores4
from   Table1
union all
select Name, EmployeeID, NULL as Scores1, Scores as Scores2, NULL as Scores3, NULL as Scores4
from   Table2
union all
select Name, EmployeeID, NULL as Scores1, NULL as Scores2, Scores as Scores3, NULL as Scores4
from   Table3
union all
select Name, EmployeeID, NULL as Scores1, NULL as Scores2, NULL as Scores3, Scores as Scores4
from   Table4
) as d
group by Name, EmployeeID


EDIT : missed out table alias

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

BankOfficerHere
Posting Yak Master

124 Posts
Posted - 2008-08-15 : 23:50:49
I tried this:

select Name, Employee, sum(Score1), sum(Score2), sum(Score3), sum(Score4)
from
(
select Name, Employee, Score as Score1, NULL as Score2, NULL as Score3, NULL as Score4
from Table1
union all
select Name, Employee, NULL as Score1, Score as Score2, NULL as Score3, NULL as Score4
from Table2
union all
select Name, Employee, NULL as Score1, NULL as Score2, Score as Score3, NULL as Score4
from Table3
union all
select Name, Employee, NULL as Score1, NULL as Score2, NULL as Score3, Score as Score4
from Table4
)
Group by Name, Employee

But...

I'm getting this message:

Server: Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'Group'.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2008-08-15 : 23:52:51
edited the prev post. Missed out the alias

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

BankOfficerHere
Posting Yak Master

124 Posts
Posted - 2008-08-15 : 23:59:32
I prefer the Outer Join method but I'm getting different results. I resulted this way:

Name EmployeeID Scores Expr1 Expr2 Expr3
Michael 111 5 9 NULL NULL
Michelle 222 10 11 11 10
Allan 333 6 NULL NULL NULL
Gilbert 444 8 7 7 8
NULL NULL NULL NULL NULL 5
NULL NULL NULL NULL NULL 6
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2008-08-16 : 00:26:38
[code]DECLARE @table1 TABLE
(
Name varchar(10),
EmployeeID int,
Scores int
)
INSERT INTO @table1
SELECT 'Michael', 111, 5 UNION ALL
SELECT 'Michelle', 222, 10 UNION ALL
SELECT 'Allan', 333, 6 UNION ALL
SELECT 'Gilbert', 444, 8

DECLARE @table2 TABLE
(
Name varchar(10),
EmployeeID int,
Scores int
)
INSERT INTO @table2
SELECT 'Michael', 111, 9 UNION ALL
SELECT 'Michelle', 222, 11 UNION ALL
SELECT 'Gilbert', 444, 7


DECLARE @table3 TABLE
(
Name varchar(10),
EmployeeID int,
Scores int
)
INSERT INTO @table3
SELECT 'Michelle', 222, 11 UNION ALL
SELECT 'Gilbert', 444, 7

DECLARE @table4 TABLE
(
Name varchar(10),
EmployeeID int,
Scores int
)
INSERT INTO @table4
SELECT 'Michael', 111, 5 UNION ALL
SELECT 'Michelle', 222, 10 UNION ALL
SELECT 'Allan', 333, 6 UNION ALL
SELECT 'Gilbert', 444, 8

SELECT [Name] = coalesce(t1.[Name], t2.[Name], t3.[Name], t4.[Name]),
EmployeeID = coalesce(t1.EmployeeID, t2.EmployeeID, t3.EmployeeID, t4.EmployeeID),
Score1 = t1.Scores,
Score2 = t2.Scores,
Score3 = t3.Scores,
Score4 = t4.Scores
FROM @table1 t1
FULL OUTER JOIN @table2 t2 ON t1.[Name] = t2.[Name] AND t1.EmployeeID = t2.EmployeeID
FULL OUTER JOIN @table3 t3 ON t1.[Name] = t3.[Name] AND t1.EmployeeID = t3.EmployeeID
FULL OUTER JOIN @table4 t4 ON t1.[Name] = t4.[Name] AND t1.EmployeeID = t4.EmployeeID

SELECT [Name], EmployeeID, Score1 = SUM(Score1), Score2 = SUM(Score2), Score3 = SUM(Score3), Score4 = SUM(Score4)
FROM
(
SELECT [Name], EmployeeID, Score1 = Scores, Score2 = NULL, Score3 = NULL, Score4 = NULL
FROM @table1

UNION ALL

SELECT [Name], EmployeeID, Score1 = NULL, Score2 = Scores, Score3 = NULL, Score4 = NULL
FROM @table2

UNION ALL

SELECT [Name], EmployeeID, Score1 = NULL, Score2 = NULL, Score3 = Scores, Score4 = NULL
FROM @table3

UNION ALL

SELECT [Name], EmployeeID, Score1 = NULL, Score2 = NULL, Score3 = NULL, Score4 = Scores
FROM @table4
) d
GROUP BY [Name], EmployeeID

/*

Name EmployeeID Score1 Score2 Score3 Score4
---------- ----------- ----------- ----------- ----------- -----------
Michael 111 5 9 NULL 5
Michelle 222 10 11 11 10
Allan 333 6 NULL NULL 6
Gilbert 444 8 7 7 8

(4 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

BankOfficerHere
Posting Yak Master

124 Posts
Posted - 2008-08-16 : 00:44:12
so it's not possible using ountput join format?
Go to Top of Page

BankOfficerHere
Posting Yak Master

124 Posts
Posted - 2008-08-16 : 00:45:58
I got it..looks like the Union All option is easier. Thanks
Go to Top of Page

BankOfficerHere
Posting Yak Master

124 Posts
Posted - 2008-08-16 : 01:38:37
I tried using the actual figures however, it didn't combine. Here's my statement:

select Empname, Datereviewed, sum(Average1), sum(Average2), sum(Average3), sum(Average4)
from
(
select Empname, Datereviewed, Average as Average1, NULL as Average2, NULL as Average3, NULL as Average4
from QACompleteAverageWK1
union all
select Empname, Datereviewed, NULL as Average1, Average as Average2, NULL as Average3, NULL as Average4
from QACompleteAverageWK2
union all
select Empname, Datereviewed, NULL as Average1, NULL as Average2, Average as Average3, NULL as Average4
from QACompleteAverageWK3
union all
select Empname, Datereviewed, NULL as Average1, NULL as Average2, NULL as Average3, Average as Average4
from QACompleteAverageWK4
) as d
group by Empname, Datereviewed


Here are my tables:

QACompleteAverageWK3
datereviewed EmpName Average


QACompleteAverageWK2
datereviewed EmpName Average
7/12/2008 QA2l 0.72072

QACompleteAverageWK3
datereviewed EmpName Average
7/26/2008 QA1 0.432432
7/26/2008 QA3 0.432432

QACompleteAverageWK4
datereviewed EmpName Average
8/4/2008 19:21 QA1 0.945945
8/4/2008 19:21 QA3 0.936936

The result I'm getting is:

Empname Datereviewed Expr1 Expr2 Expr3 Expr4
QA1 7/26/2008 0.432432
QA1 8/4/2008 19:21 0.945945
QA2l 7/12/2008 0.72072
QA3 7/26/2008 0.432432
QA3 8/4/2008 19:21 0.936936


Where it should be:

Empname Datereviewed Expr1 Expr2 Expr3 Expr4
QA1 7/26/2008 0.432432 0.945945
QA2l 7/12/2008 0.72072
QA3 7/26/2008 0.432432 0.936936
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-08-16 : 02:07:53
it seems like what you want is this

select Empname, MIN(Datereviewed), min(Average1), min(Average2), min(Average3), min(Average4)
from
(
select Empname, Datereviewed, Average as Average1, NULL as Average2, NULL as Average3, NULL as Average4
from QACompleteAverageWK1
union all
select Empname, Datereviewed, NULL as Average1, Average as Average2, NULL as Average3, NULL as Average4
from QACompleteAverageWK2
union all
select Empname, Datereviewed, NULL as Average1, NULL as Average2, Average as Average3, NULL as Average4
from QACompleteAverageWK3
union all
select Empname, Datereviewed, NULL as Average1, NULL as Average2, NULL as Average3, Average as Average4
from QACompleteAverageWK4
) as d
group by Empname, Datereviewed
Go to Top of Page

BankOfficerHere
Posting Yak Master

124 Posts
Posted - 2008-08-16 : 02:22:03
I found out the problem, however, is it possible that for NULL results, I want to be zero (0) instead. Because it dreamweaver doesn't accepts NULL. Here's my statement:

select [Empname], sum(Average1), sum(Average2), sum(Average3), sum(Average4)
from
(
select [Empname], Average as Average1, NULL as Average2, NULL as Average3, NULL as Average4
from QACompleteAverageWK1
union all
select [Empname], NULL as Average1, Average as Average2, NULL as Average3, NULL as Average4
from QACompleteAverageWK2
union all
select [Empname], NULL as Average1, NULL as Average2, Average as Average3, NULL as Average4
from QACompleteAverageWK3
union all
select [Empname], NULL as Average1, NULL as Average2, NULL as Average3, Average as Average4
from QACompleteAverageWK4
) as d
group by [Empname]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-08-16 : 02:24:37
quote:
Originally posted by BankOfficerHere

I found out the problem, however, is it possible that for NULL results, I want to be zero (0) instead. Because it dreamweaver doesn't accepts NULL. Here's my statement:

select [Empname], sum(Average1), sum(Average2), sum(Average3), sum(Average4)
from
(
select [Empname], Average as Average1, 0 as Average2, 0 as Average3, NULL as Average4
from QACompleteAverageWK1
union all
select [Empname], 0 as Average1, Average as Average2, 0 as Average3, 0 as Average4
from QACompleteAverageWK2
union all
select [Empname], 0 as Average1, 0 as Average2, Average as Average3, NULL as Average4
from QACompleteAverageWK3
union all
select [Empname], 0 as Average1, 0 as Average2, 0 as Average3, Average as Average4
from QACompleteAverageWK4
) as d
group by [Empname]

put 0's instead of NULLs
Go to Top of Page

BankOfficerHere
Posting Yak Master

124 Posts
Posted - 2008-08-16 : 02:32:35
that shouldn't work but i did figure it out..thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-08-16 : 02:44:52
quote:
Originally posted by BankOfficerHere

that shouldn't work but i did figure it out..thanks

why? did you get any error?
Go to Top of Page

BankOfficerHere
Posting Yak Master

124 Posts
Posted - 2008-08-16 : 11:58:21
yes you just can't change it to zero. you need the statement coalesce
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-08-16 : 12:49:31
quote:
Originally posted by BankOfficerHere

yes you just can't change it to zero. you need the statement coalesce

didnt get thet...you're just hardcoding it. then why use coalesce?
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site