Link to home
Start Free TrialLog in
Avatar of ShawnGray
ShawnGray

asked on

Visual Studio SQL Join

Having trouble with a 1 to many join in a sql query.
Seems its only returning PortfolioCodes when it finds a TradeDate.
How can I get all PortfolioCodes to show even if it doesn't have a TradeDate?

SELECT     AdvApp.vPortfolio.PortfolioCode, AdvApp.vPortfolioTransaction.TradeDate
FROM         AdvApp.vPortfolio LEFT OUTER JOIN
                      AdvApp.vPortfolioTransaction ON AdvApp.vPortfolio.PortfolioID = AdvApp.vPortfolioTransaction.PortfolioID
WHERE     (AdvApp.vPortfolioTransaction.TradeDate = '9/25/13')
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Since it's a left join it's possible there could be no transactions, therefore you need to include the possibility of null
SELECT     AdvApp.vPortfolio.PortfolioCode, AdvApp.vPortfolioTransaction.TradeDate
FROM         AdvApp.vPortfolio LEFT OUTER JOIN
                      AdvApp.vPortfolioTransaction ON AdvApp.vPortfolio.PortfolioID = AdvApp.vPortfolioTransaction.PortfolioID
WHERE     (AdvApp.vPortfolioTransaction.TradeDate = '9/25/13' or AdvApp.vPortfolioTransaction is null)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of TempDBA
TempDBA
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
As TempDBA has alluded to (no points please), by adding that condition you have essentially converted the query to an implicit INNER JOIN.

Also, you need to get used to using aliases, it makes it easier to read for you and everyone else.  As in:
SELECT  p.PortfolioCode,
        t.TradeDate
FROM    AdvApp.vPortfolio p
        LEFT OUTER JOIN AdvApp.vPortfolioTransaction t ON p.PortfolioID = t.PortfolioID
                                                          AND t.TradeDate = '20130925'

Open in new window

Avatar of ShawnGray
ShawnGray

ASKER

Thank you