outer join - 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

SQL Server 2012 Forums

Analysis Server and Reporting Services (2012)

outer join

Author

Topic

DanielS
Starting Member

32 Posts

Posted - 2013-12-26 : 17:11:03

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

52326 Posts

Posted - 2013-12-28 : 01:35:43

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

DanielS
Starting Member

32 Posts

Posted - 2013-12-29 : 15:55:28

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.

DanielS
Starting Member

32 Posts

Posted - 2014-01-13 : 17:51:01

Any other thoughts on this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-14 : 04:24:44

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

DanielS
Starting Member

32 Posts

Posted - 2014-01-14 : 23:48:16

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.

DanielS
Starting Member

32 Posts

Posted - 2014-01-14 : 23:49:55

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-15 : 06:16:18

[code]
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'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

DanielS
Starting Member

32 Posts

Posted - 2014-01-15 : 07:05:19

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources