Trying to write a troublesome sql join

Question

Table OPP

OppA     Channel1     Value1
OppA     Channel2     Value2
OppA     Channel3     Value3

Table Source

OppA     Channel1     Value4
OppA     Channel2     Value5
OppA     Channel4     Value6

Desired outcome:

OppA     Channel1     Value1     Value4
OppA     Channel2     Value2     Value5
OppA     Channel3     Value3     null
OppA     Channel4     null       Value6

I thought I would get my result by doing the join

Select OppName, Channel, OppValue, SourceValue
from Opp 
full join Source on Opp.OppName = Source.SourceName and OppChannel = SourceChannel

When that didn't work I tried

Select OppName, Channel, OppValue, SourceValue
from Opp 
full outer join Source on OppChannel = SourceChannel
where Opp.OppName=OppA and Source.OppName=OppA

But either way I only get 2 rows returned. (The two rows with no null values)

Please help! (and, thanks in advance)

Duplicate of [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/q/4707673/3404097) LEFT JOIN ON returns INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN ON you want as part of an OUTER JOIN ON. After a LEFT JOIN ON, a WHERE or INNER JOIN that requires a right [sic] table column to be not NULL removes any rows with introduced NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. PS This is an easily searched faq. — philipxy, Jun 11 '20 at 14:21
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Jun 11 '20 at 14:22
Thanks philipxy. That didn't fix my issue entirely, but it got me well down the path. — read0063, Jun 11 '20 at 14:57
You don't clearly say how output is a function of input, you just give an example to guess from, with very few input row cases, so it's difficult to help you further. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. Say enough that someone could go away & come back with a solution & a user knows how to use it. PS Oops I gave you the left not full join comment version. — philipxy, Jun 11 '20 at 15:05

score 0 · Answer 1 · answered Jun 11 '20 at 14:20

0

I think you just need to handle the NULL values:

select coalesce(o.OppName, s.SourceName) as name,
       coalesce(o.OppChannel, s.SourceChannel) as Channel,
       o.OppValue, s.SourceValue
from Opp o full join
     Source s
     on o.OppName = s.SourceName and o.OppChannel = s.SourceChannel

answered Jun 11 '20 at 14:20

Gordon Linoff

1,242,037
58
646
786

Thanks for the hint. I'll try that. And, thanks for editing my post for formatting. I couldn't figure that out. And, even looking at your edited version I'm not sure what you did there. – read0063 Jun 11 '20 at 14:22
@read0063 . . . If you indent by four spaces, you get the "code" formatting. You can also surround the block with three backticks before and after for the same affect -- but that is newer functionality. – Gordon Linoff Jun 11 '20 at 14:24
Still only 2 rows returned. – read0063 Jun 11 '20 at 14:36
2

@read0063 Try again. it works exactly as you describe with the tables and data you posted - [fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=b350c871b7bc02de2f224511ea796770). And this is why YOU (not volunteers) should post a MVCE so that everyone has the same starting point for your question. – SMor Jun 11 '20 at 15:53

Trying to write a troublesome sql join

1 Answers1