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 2008 Forums
 Transact-SQL (2008)
 2 tables left and right outer join 1 column

Author  Topic 

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-04-26 : 15:27:02
Ok very simple request. I created 2 views that have the same fields except 1. I am trying to get Posted and unposted on 1 line. and the sa-no all in the first column. Below you will see expr1 that is sa-seq-no and Expr2 that is sa-no those are from table 2. I gave you an example of what the results are I am getting I will show you what I am trying to get below that.

SELECT TOP (100) PERCENT
dbo.[sa-earnviewpost].[sa-no],
dbo.[sa-earnviewpost].[sa-seq-no],
dbo.[sa-earnviewpost].Posted,
dbo.[sa-earnviewunpost].Unposted,
dbo.[sa-earnviewunpost].[sa-seq-no] AS Expr1,
dbo.[sa-earnviewunpost].[sa-no] AS Expr2

FROM dbo.[sa-earnviewpost] FULL OUTER JOIN
dbo.[sa-earnviewunpost] ON dbo.[sa-earnviewpost].[sa-no] = dbo.[sa-earnviewunpost].[sa-no] AND dbo.[sa-earnviewpost].[sa-seq-no] = dbo.[sa-earnviewunpost].[sa-seq-no]

ORDER BY dbo.[sa-earnviewpost].[sa-no]

sa-no sa-seq-no Posted Unposted Expr1 expr2
NULL NULL NULL 120.00 2 W0039601
NULL NULL NULL 156.00 2 X0028709
NULL NULL NULL 100.00 1 X0028715
NULL NULL NULL 120.00 2 X0028722
20040097 1 66.36 132.64 1 20040097
20040109 1 96.36 192.64 1 20040109
20040115 1 66.36 132.64 1 20040115
20040134 1 66.36 132.64 1 20040134
20040140 1 66.36 132.64 1 20040140
I0015035 2 218.00 NULL NULL NULL
I0015040 2 218.00 NULL NULL NULL
I0015041 1 218.00 NULL NULL NULL
I0015044 2 218.00 NULL NULL NULL
I0015045 1 218.00 NULL NULL NULL
I0015048 2 218.00 NULL NULL NULL
I0015049 1 218.00 NULL NULL NULL
I0015050 1 87.20 130.80 1 I0015050


What I am trying to get.


sa-no sa-seq-no Posted Unposted Expr1 expr2
W0039601 2 NULL 120.00 2 W0039601
X0028709 2 NULL 156.00 2 X0028709
X002871 1 NULL 100.00 1 X0028715
X0028722 2 NULL 120.00 2 X0028722
I0015035 2 218.00 NULL NULL NULL
I0015040 2 218.00 NULL NULL NULL
I0015041 1 218.00 NULL NULL NULL
I0015044 2 218.00 NULL NULL NULL
I0015045 1 218.00 NULL NULL NULL
I0015048 2 218.00 NULL NULL NULL
I0015049 1 218.00 NULL NULL NULL
20040097 1 66.36 132.64 1 20040097
20040109 1 96.36 192.64 1 20040109
20040115 1 66.36 132.64 1 20040115
20040134 1 66.36 132.64 1 20040134
20040140 1 66.36 132.64 1 20040140

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-27 : 10:10:36
You may need a where clause to filter what you need or one more condition on joining the two views...
It is difficult to answer your question with the given details.

Provide more details as specified at this site if you need more help:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 00:55:50
sounds like this to me


SELECT TOP (100) PERCENT
COALESCE(dbo.[sa-earnviewpost].[sa-no],dbo.[sa-earnviewunpost].[sa-no]) AS [sa-no],
COALESCE(dbo.[sa-earnviewpost].[sa-seq-no],dbo.[sa-earnviewunpost].[sa-seq-no]) AS [sa-seq-no],
dbo.[sa-earnviewpost].Posted,
dbo.[sa-earnviewunpost].Unposted,
dbo.[sa-earnviewunpost].[sa-seq-no] AS Expr1,
dbo.[sa-earnviewunpost].[sa-no] AS Expr2

FROM dbo.[sa-earnviewpost] FULL OUTER JOIN
dbo.[sa-earnviewunpost] ON dbo.[sa-earnviewpost].[sa-no] = dbo.[sa-earnviewunpost].[sa-no] AND dbo.[sa-earnviewpost].[sa-seq-no] = dbo.[sa-earnviewunpost].[sa-seq-no]

ORDER BY COALESCE(dbo.[sa-earnviewpost].[sa-no],dbo.[sa-earnviewunpost].[sa-no])


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-04-29 : 07:27:13
Perfect, thank you visakh16
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 07:29:32
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -