FULL OUTER JOIN which is somewhat INNER

Question

Here is the problem I'm facing to :

I receive expected Xs from the client
I receive realized Xs from the firm agencies
I have to full outer join the expected and realized Xs on code equality AS LONG AS THEY BELONG TO THE SAME JOB FILE

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 ! :-)

Could you paste the structure of all your tables, perhaps with some sample data? — EdoDodo, Jul 05 '11 at 15:07
do you have data in your real format we could look at? it's hard to give you something if we don't know what the original looks like — DForck42, Jul 05 '11 at 15:09

score 6 · Answer 1 · answered Jul 05 '11 at 15:10

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

score 2 · Answer 2 · answered Jul 05 '11 at 15:10

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

score 2 · Answer 3 · answered Jul 05 '11 at 15:13

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

score 2 · Answer 4 · answered Jul 05 '11 at 15:13

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

Ssithra · Answer 5 · 2011-07-05T15:33:42.273

1

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...

edited Jul 05 '11 at 15:33

answered Jul 05 '11 at 15:25

Ssithra

710
3
8

1

Actually this kind of annoys me; it sounds like your business details are so private that you can't post enough information to even give us a chance at answering your actual question. I see no evidence that you even looked at the answers.... so why waste everyone's time by posting the question in the first place? – Chris Cunningham Jul 05 '11 at 15:47
@Chris The business details are not private but I felt like it would become far less easy to describe case and would mix different kinds of problems. Basically, all job files have realized Xs and just a relatively small number of them have also expected Xs which enter the scope of my post. A pure full outer join would take all the realized Xs into account, that is thousands of lines too many. – Ssithra Jul 06 '11 at 07:35

FULL OUTER JOIN which is somewhat INNER

5 Answers5