-1

This is my query:

SELECT  

a.[Hierarchy Group ID],
o.[Opportunity ID],
o.[Opportunity Name],
o.[ARR],
o.[TCM],
p.[Product Group],
a.[Account Management Scope],


FROM OpportunityTable as o 
LEFT JOIN AccountTable as a ON o.[Account ID]=a.[Account ID] 
LEFT JOIN ProductTable as p ON o.[Product Lookup]=p.[Product ID]

WHERE 
o.[Stage] = 'Value' 
a.[Account Management Scope] in ('Value 1','Value 2','Value 3')

When I run this query, I get null values for some rows (but not all) for Hierarchy Group ID and Account Management Scope even though I know that these values are not null. However, when I add an additional "WHERE [Account ID] = 'Specific Value'" the values for Hierarchy Group ID and Account Management Scope suddenly appear for that particular account that just showed null values. Is there something wrong with the way I am joining these three tables that is causing this to happen?

double-beep
  • 5,031
  • 17
  • 33
  • 41
J.Sun
  • 13
  • 2
  • 1
    You are negating your `outer join` on the `accounttable` since you include `where` criteria with it. As is though, I don't see anything else wrong with your sql. Can you post sample data and desired results? – sgeddes Dec 10 '15 at 19:05
  • I am new to stackoverflow. How do you post sql results? – J.Sun Dec 10 '15 at 19:21
  • 1
    The easiest way is to create a sqlfiddle.com replicating your problem. However, at minimum you'd need to post sample data from each of your tables and desired results (just edit your question with the data). – sgeddes Dec 10 '15 at 19:25
  • [mre] please. PS Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. – philipxy Aug 11 '19 at 18:48

1 Answers1

0

Because you're using left join, the result will have a row for every row in OpportunityTable, whether or not there is a matching row in the other tables. When no matching row for join, you will get NULLs for the columns in the other table(s).

DBug
  • 2,502
  • 1
  • 12
  • 25
  • It is showing null for rows that do have a match though. The nulls turn into values when I specify a particular Account ID however. – J.Sun Dec 10 '15 at 19:06
  • You'll get NULLs for every row in OpportunityTable that does not match BOTH tables. If you think it does match both and still getting nulls, may help to show some sample data, along with the result – DBug Dec 10 '15 at 19:08