FULL JOIN PROBLEMS - SQL Server Forums

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

FULL JOIN PROBLEMS

Author

Topic

Fredforfree
Starting Member

49 Posts

Posted - 2010-11-20 : 13:51:07

Hi

I can't figure out how to get the information on the left to show in each row that match not just the first.

PROC SQL;
CREATE TABLE TEST AS
SELECT A.DATE,A.DAY,A.HANDLED,A.LEVEL1DESC, P.UCL,P.LCL
FROM MEAN1 A FULL OUTER JOIN DAILY_MEAN P
ON A.LEVEL1DESC = P.LEVEL1DESC AND A.DAY = P.DAY

ORDER BY A.LEVEL1DESC, A.DATE;
QUIT;

fred

Fred

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-20 : 16:56:30

well that's not sql server..DB2? Oracle?

SELECT A.DATE,A.DAY,A.HANDLED,A.LEVEL1DESC, P.UCL,P.LCL
INTO TEST
FROM MEAN1 A FULL OUTER JOIN DAILY_MEAN P
ON A.LEVEL1DESC = P.LEVEL1DESC AND A.DAY = P.DAY
ORDER BY A.LEVEL1DESC, A.DATE;

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

singularity
Posting Yak Master

153 Posts

Posted - 2010-11-20 : 17:07:54

Looks like SAS to me. Not sure I understand what OP is trying to accomplish though. Sample data and expected output might help.

Fredforfree
Starting Member

49 Posts

Posted - 2010-11-20 : 18:32:18

Hello

SQL code is going to be used i SAS yes, sorry.

First table is Mean1

Day Date Handled LEVEL1DESC

Monday Nov,1 2010 234321 Home320
Tuesday Nov,2 2010 545655 Home320
Wednesday Nov,3 2010 474848 Home320
Thurday Nov,4 2010 847478 Home320
Friday Nov,5 2010 334556 Home320
Saturday Nov,6 2010 545656 Home320
Sunday Nov,7 2010 33445 Home320
Monday Nov,8 2010 234321 Home320
Tuesday Nov,9 2010 545655 Home320
Wednesday Nov,10 2010 474848 Home320
Thurday Nov,11 2010 847478 Home320
Friday Nov,12 2010 484111 Home320
Saturday Nov,13 2010 555656 Home320
Sunday Nov,14 2010 133445 Home320

Table 2 Daily_Mean

Day LEVEL1DESC UCL LCL

Monday Home320 122812 447585
Tuesday Home320 477373 448885
Wednesday Home320 463748 484758
Thurday Home320 343535 488858
Friday Home320 425256 857774
Saturday Home320 243453 858577
Sunday Home320 557757 585885

Table 3 test expected outcome

Day Date Handled LEVEL1DESC UCL LCL

Monday Nov,1 2010 234321 Home320 122812 447585
Tuesday Nov,2 2010 545655 Home320 477373 448885
Wednesday Nov,3 2010 474848 Home320 463748 484758
Thurday Nov,4 2010 847478 Home320 343535 488858
Friday Nov,5 2010 334556 Home320 425256 857774
Saturday Nov,6 2010 545656 Home320 243543 858577
Sunday Nov,7 2010 33445 Home320 557757 585885
Monday Nov,8 2010 234321 Home320 122812 447585
Tuesday Nov,9 2010 545655 Home320 477373 448885
Wednesday Nov,10 2010 474848 Home320 463748 484758
Thurday Nov,11 2010 847478 Home320 343535 488858
Friday Nov,12 2010 484111 Home320 425256 857774
Saturday Nov,13 2010 555656 Home320 243543 858577
Sunday Nov,14 2010 133445 Home320 577757 585885

RIGHT join, left join or full join do not seem to work.

Help please

fred

Fred

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-20 : 18:42:58

Looks like

SELECT A.DATE,A.DAY,A.HANDLED,A.LEVEL1DESC, P.UCL,P.LCL
FROM MEAN1 A
JOIN DAILY_MEAN P
ON A.LEVEL1DESC = P.LEVEL1DESC
AND A.DAY = P.DAY
ORDER BY A.LEVEL1DESC, A.DATE

but the other join types should all give the same result

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Fredforfree
Starting Member

49 Posts

Posted - 2010-11-21 : 09:07:53

Hi nigelrivett

I've tried that but that results in only the first record for each day showing up in table 3, Nov, 1 2010 to Nov, 7 2010. I need the results for UCL and LCL to repeat for each Date.

Any suggestions?

Fred

Fred

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-21 : 12:40:18

Not sure I understand.
It looks like you want each row in table1 with the ucl and lcl from table2 for that weekday appended.
That's what that should give (if day really is the dayname in both tables).
In your eample table3 it looks like there is one row per day just like table1.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Fredforfree
Starting Member

49 Posts

Posted - 2010-11-22 : 07:06:39

Hi

Table 3 expected outcome has two weeks of data, after your query the results look more like table 2 containing only one week of data with UCL & LCL Nov,1 2010 to Nov,7 2010.

Merging of the tables was successful but it is stopping after the first record for each day?

HELP

Fred

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-22 : 10:41:05

Think there's something you're not telling us about the data

declare @t1 table (Day varchar(20), Date datetime, Handled int, LEVEL1DESC varchar(100))
declare @t2 table (Day varchar(20), LEVEL1DESC varchar(100), UCL int, LCL int)

insert @t1 select 'Monday', 'Nov,1 2010', 234321, 'Home320'
insert @t1 select 'Tuesday', 'Nov,2 2010', 545655, 'Home320'
insert @t1 select 'Wednesday', 'Nov,3 2010', 474848, 'Home320'
insert @t1 select 'Thurday', 'Nov,4 2010', 847478, 'Home320'
insert @t1 select 'Friday', 'Nov,5 2010', 334556, 'Home320'
insert @t1 select 'Saturday', 'Nov,6 2010', 545656, 'Home320'
insert @t1 select 'Sunday', 'Nov,7 2010', 33445, 'Home320'
insert @t1 select 'Monday', 'Nov,8 2010', 234321, 'Home320'
insert @t1 select 'Tuesday', 'Nov,9 2010', 545655, 'Home320'
insert @t1 select 'Wednesday', 'Nov,10 2010', 474848, 'Home320'
insert @t1 select 'Thurday', 'Nov,11 2010', 847478, 'Home320'
insert @t1 select 'Friday', 'Nov,12 2010', 484111, 'Home320'
insert @t1 select 'Saturday', 'Nov,13 2010', 555656, 'Home320'
insert @t1 select 'Sunday', 'Nov,14 2010', 133445, 'Home320'

insert @t2 select 'Monday', 'Home320', 122812, 447585
insert @t2 select 'Tuesday', 'Home320', 477373, 448885
insert @t2 select 'Wednesday', 'Home320', 463748, 484758
insert @t2 select 'Thurday', 'Home320', 343535, 488858
insert @t2 select 'Friday', 'Home320', 425256, 857774
insert @t2 select 'Saturday', 'Home320', 243453, 858577
insert @t2 select 'Sunday', 'Home320', 557757, 585885

SELECT A.DATE,A.DAY,A.HANDLED,A.LEVEL1DESC, P.UCL,P.LCL
FROM @t1 A
JOIN @t2 P
ON A.LEVEL1DESC = P.LEVEL1DESC
AND A.DAY = P.DAY
ORDER BY A.LEVEL1DESC, A.DATE
DATE DAY HANDLED LEVEL1DESC UCL LCL
----------------------- -------------------- ----------- --------------- ----------- -----------
2010-11-01 00:00:00.000 Monday 234321 Home320 122812 447585
2010-11-02 00:00:00.000 Tuesday 545655 Home320 477373 448885
2010-11-03 00:00:00.000 Wednesday 474848 Home320 463748 484758
2010-11-04 00:00:00.000 Thurday 847478 Home320 343535 488858
2010-11-05 00:00:00.000 Friday 334556 Home320 425256 857774
2010-11-06 00:00:00.000 Saturday 545656 Home320 243453 858577
2010-11-07 00:00:00.000 Sunday 33445 Home320 557757 585885
2010-11-08 00:00:00.000 Monday 234321 Home320 122812 447585
2010-11-09 00:00:00.000 Tuesday 545655 Home320 477373 448885
2010-11-10 00:00:00.000 Wednesday 474848 Home320 463748 484758
2010-11-11 00:00:00.000 Thurday 847478 Home320 343535 488858
2010-11-12 00:00:00.000 Friday 484111 Home320 425256 857774
2010-11-13 00:00:00.000 Saturday 555656 Home320 243453 858577
2010-11-14 00:00:00.000 Sunday 133445 Home320 557757 585885

result

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources