SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 2 tables left and right outer join 1 column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hbadministrator
Posting Yak Master

109 Posts

Posted - 04/26/2013 :  15:27:02  Show Profile  Reply with Quote
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

Edited by - hbadministrator on 04/26/2013 15:28:25

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/27/2013 :  10:10:36  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 04/29/2013 :  00:55:50  Show Profile  Reply with Quote
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

109 Posts

Posted - 04/29/2013 :  07:27:13  Show Profile  Reply with Quote
Perfect, thank you visakh16
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/29/2013 :  07:29:32  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000