CODEWITHSUNDEEP
sqlsql-serverinner-joinfull-outer-join
Ssithra
Ssithra

Reputation: 710

FULL OUTER JOIN which is somewhat INNER

Here is the problem I'm facing to :

For example :

Realized Xs for job file #123 : A and B
Expected Xs for job file #123 : A and C
Expected Xs for job file #456 : B

Expected result :

Job_File Realized Expected
-------- -------- --------
123      A        A
123      B        NULL
123      NULL     C
456      NULL     B

Of course 123's realized B should NEVER match with 456's expected B since they don't belong to the same job file.

So, it is as if I wanted to simultaneously full outer join on the code and inner join on the job file id.

That seems weird but... very logical, in fact. How on earth could I do such a thing ???

Thank you very much in advance for your precious help ! :-)

Upvotes: 2

Views: 209

Answers (5)

Ssithra
Ssithra

Reputation: 710

Your answers are all correct given the way the question was posted. Thank you all for how quick you answered ! :-)

I couldn't easily explain why unfortunately it can't apply to my specific context without adding too much specific business details to the post. A pure FULL OUTER JOIN would result in adding billions of unwanted non matching lines.

Fortunately a colleague found the trick, and I give it back in turn, in case someone could recognize his own problem in mine, despite how vague I keep it : the idea is (simply) to INNER JOIN the realized on the expected by job file id and then to FULL OUTER JOIN the result to the expected once again, by code this time.

Hope this could help someone...

Upvotes: 1

AndrewBay
AndrewBay

Reputation: 938

This should work, as it is a full outer join as you said yourself:

select
   isnull(r.JobFile, e.JobFile) as JobFile, Realized, Expected
from
   (select 123 as JobFile, 'A' as Realized
   union all
   select 123, 'B')  r

   full outer join
   (select 123 as JobFile, 'A' as Expected
   union all
   select 123, 'C'
   union all
   select 456, 'B')  e on r.JobFile = e.JobFile and r.Realized = e.Expected

   order by 1, 2

Upvotes: 2

Frank Schmitt
Frank Schmitt

Reputation: 30845

A full outer join should work just fine (assuming SQL Server supports it, I checked only using Oracle):

create table realized_xs(job_file number, module varchar2(10));
create table expected_xs(job_file number, module varchar2(10));

insert into realized_xs(job_file, module) values(123, 'A');
insert into realized_xs(job_file, module) values(123, 'B');
insert into expected_xs(job_file, module) values(123, 'A');
insert into expected_xs(job_file, module) values(123, 'C');
insert into expected_xs(job_file, module) values(456, 'B');

select coalesce(r.job_file, e.job_file) job_file, 
 r.module r_module, e.module e_module
from realized_xs r 
full outer join expected_xs e on r.job_file = e.job_file and r.module = e.module

Upvotes: 2

Chris Cunningham
Chris Cunningham

Reputation: 1876

So, it is as if I wanted to simultaneously full outer join on the code and inner join on the job file id.

I'll assume that you have these tables:

Jobs
  id

Realized
  id
  job_id

Expected
  id
  job_id

Then you can do exactly what you said you wanted to do!

SELECT j.job_id, r.id, e.id
FROM Jobs j
INNER JOIN (Realized r FULL OUTER JOIN Expected e
            ON r.job_id = e.job_id)
ON j.id = r.job_id

Upvotes: 2

Cyril Gandon
Cyril Gandon

Reputation: 17068

You just need to join on two conditions instead of one !

SELECT COALESCE(realized.Job_File, expected.Job_File), 
       realized.code, 
       expected.code
FROM realized 
    FULL OUTER JOIN expected 
        ON realized.Job_File = expected.Job_File 
        AND realized.Code = expected.Code

Upvotes: 6

Related Questions