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 2012 Forums
 Analysis Server and Reporting Services (2012)
 outer join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DanielS
Starting Member

Australia
32 Posts

Posted - 12/26/2013 :  17:11:03  Show Profile  Reply with Quote
Hi, I'm having issues with an outer join. I have a portfolio and an index. I want to see all securities within the portfolio and the index. When the securities are common to both tables I expect the results to appear on the same line, but if a security exists in the portfolio table but not the index table, then values should appear for the portfolio and nulls for the index, and vice versa. Below is my query. What appears to be happening is that every security in the index table is being shown against every security in the portfolio table, so I'm seeing multiple records.


select A.PDATE,
A.PORT,
A.CODE,
A.WGT,
B.IDATE,
B.IDX,
B.CODE,
B.WGT
from PORTFOLIO A
full outer join INDX B
on A.PDATE = B.IDATE
where A.PDATE = '2013-12-20'
and A.PORT = 'ABC'
and B.IDX = 'XYZ'

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/28/2013 :  01:35:43  Show Profile  Reply with Quote
Thats because of the join condition. You're just joining on date field so all policies on a date will get joined against all indexes on that date which is why you see duplicates.Do you've any other way of relating between a unique portfolio and unique index record?

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

DanielS
Starting Member

Australia
32 Posts

Posted - 12/29/2013 :  15:55:28  Show Profile  Reply with Quote
The only other field I can join on is CODE. So when I add to the ON condition 'and A.CODE = B.CODE' the results I receive are only a subset of what I'm after. I get results common to both PORTFOLIO and INDX tables, but I want to see everything.
Go to Top of Page

DanielS
Starting Member

Australia
32 Posts

Posted - 01/13/2014 :  17:51:01  Show Profile  Reply with Quote
Any other thoughts on this?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/14/2014 :  04:24:44  Show Profile  Reply with Quote
quote:
Originally posted by DanielS

Any other thoughts on this?


Not until we see some sample data from your tables to understand how they're related and your required output to know what you're after!

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

DanielS
Starting Member

Australia
32 Posts

Posted - 01/14/2014 :  23:48:16  Show Profile  Reply with Quote
Sure. I've tried to simplify my table in this excample as there are a lot of other fields, for the most part descriptive.

The PORTFOLIO table contains a date (PDATE), a portfolio code (PORT) a security code (CODE) and a weight (WGT).

PDATE PORT CODE WGT
14/01/2013 ABC AU123 0.2
14/01/2013 ABC AU456 0.25
14/01/2013 ABC AU789 0.35
14/01/2013 ABC US123 0.15
14/01/2013 ABC CASH 0.05

The INDX table contains a date (IDATE) and index code (INDX) a security code (CODE) as per the PORTFOLIO table and also a weight (WGT).

IDATE INDX CODE WGT
14/01/2013 XYZ AU123 0.15
14/01/2013 XYZ AU456 0.2
14/01/2013 XYZ AU321 0.12
14/01/2013 XYZ AU246 0.17
14/01/2013 XYZ AU468 0.15
14/01/2013 XYZ AU369 0.21


The output I wish to achive will show me the WGT for all CODE's in both tables, but with zeros or nulls when they don't exist the other table. So the sum of all WGTs should always be 1. My output should look like this:

PDATE PORT IDX CODE PWGT IWGT
14/01/2013 ABC XYZ AU123 0.2 0.15
14/01/2013 ABC XYZ AU456 0.25 0.2
14/01/2013 ABC XYZ AU789 0.35
14/01/2013 ABC XYZ US123 0.15
14/01/2013 ABC XYZ CASH 0.05
14/01/2013 ABC XYZ AU321 0.12
14/01/2013 ABC XYZ AU246 0.17
14/01/2013 ABC XYZ AU468 0.15
14/01/2013 ABC XYZ AU369 0.21

When I run the code I provided earlier with an full outer join I get every CODE/WGT from the INDX table appearing for each CODE in the PORTFOLIO table, ie many duplicates.

If I add another condition to my join, where I join the PORTFOLIO.CODE and the INDX.CODE, then I only get results which appear in both tables, ie the first 2 lines in the above sample.

I hope this makes sense.



Go to Top of Page

DanielS
Starting Member

Australia
32 Posts

Posted - 01/14/2014 :  23:49:55  Show Profile  Reply with Quote
Sorry, that example output table didn't come out too well, here's another attempt.

PDATE PORT IDX CODE PWGT IWGT
14/01/2013 ABC XYZ AU123 0.2 0.15
14/01/2013 ABC XYZ AU456 0.25 0.2
14/01/2013 ABC XYZ AU789 0.35 NULL
14/01/2013 ABC XYZ US123 0.15 NULL
14/01/2013 ABC XYZ CASH 0.05 NULL
14/01/2013 ABC XYZ AU321 NULL 0.12
14/01/2013 ABC XYZ AU246 NULL 0.17
14/01/2013 ABC XYZ AU468 NULL 0.15
14/01/2013 ABC XYZ AU369 NULL 0.21
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/15/2014 :  06:16:18  Show Profile  Reply with Quote

select COALESCE(A.PDATE,B.IDATE) AS [DATE],
A.PORT,
B.IDX,
COALESCE(A.CODE,B.CODE) AS CODE,
A.WGT,
B.WGT
from PORTFOLIO A
full outer join INDX B
on A.PDATE = B.IDATE
AND A.CODE = B.CODE
WHERE COALESCE(A.PDATE,B.IDATE) = '2013-12-20'


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

DanielS
Starting Member

Australia
32 Posts

Posted - 01/15/2014 :  07:05:19  Show Profile  Reply with Quote
Hi. I still don't seem to get all the records from both tables. The results only display CODEs which exist in both tables.

In the where statement at the end, I've also had to add:
and PORT = 'ABC'
and INDX = 'XYZ'
for my test code as I have a number of different entries for PORT and INDX. These will be parameterised at a later point.

Nonetheless, with or without these additional 'and' statements I don't get the results I'm after.
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.09 seconds. Powered By: Snitz Forums 2000