I'm looking for a way to archive the following:

Imagine Tables A, B:

A:

aID, aID2, avalue
=================
1  , 10  , 'abc'
2  , 20  , 'def'
3  , 30  , 'ghi'
4  , 40  , 'jkl'

B:

bID, bID2, bvalue
=================
1  , 10  , 'mno'
20 , 20  , 'pqr'
3  , 1   , 'stu'

Now look at the following SQL statement and results (I'm on Oracle 11, but should be the same for MSSQL):

SELECT A.*, B.* FROM A LEFT OUTER JOIN B ON (A.aID = B.bID)

aID, aID2, avalue, bID , bID2, bvalue
=====================================
1  , 10  , 'abc' , 1   , 10  , 'mno'  
2  , 20  , 'def' , NULL, NULL, NULL
3  , 30  , 'ghi' , 3   , 1   , 'stu'  
4  , 40  , 'jkl' , NULL, NULL, NULL

SELECT A.*, B.* FROM A LEFT OUTER JOIN B ON (A.aID = B.bID AND A.aID2 = B.bID2)

aID, aID2, avalue, bID , bID2, bvalue
=====================================
1  , 10  , 'abc' , 1   , 10  , 'mno'  
2  , 20  , 'def' , NULL, NULL, NULL
3  , 30  , 'ghi' , NULL, NULL, NULL
4  , 40  , 'jkl' , NULL, NULL, NULL

Fine so far.

I'm looking for a statement (as easy as possible), that gets me the following:

MADE-UP-CODE: SELECT A.*, B.* FROM A LEFT OUTER JOIN B ON (A.aID = B.bID AND A.aID2 = B.bID2 KEEP MATCHING COLS)

aID, aID2, avalue, bID , bID2, bvalue
=====================================
1  , 10  , 'abc' , 1   , 10  , 'mno'  
2  , 20  , 'def' , NULL, 20  , NULL    (note 20)
3  , 3开发者_开发问答0  , 'ghi' , 3   , NULL, NULL    (note 3)
4  , 40  , 'jkl' , NULL, NULL, NULL

Is there a way to get this behavior (keep matching parts, NULL not matching parts of "ON" clause and all value columns) using only joins while not using self-joins over and over?

What way would you suggest if there is no keyworld like "KEEP MATCHING COLS"? Subselect? Selfjoins?

Thanks, Blama

Join on Id or Id2 and then selectively null out the results in the select clause.

Set up test tables and data:

set null 'NULL'
create table a (aId number
    , aId2 number
    , aValue varchar2(4));
insert into a values (1, 10, 'abc');
insert into a values (2, 20, 'def');
insert into a values (3, 30, 'ghi');
insert into a values (4, 40, 'jkl');
create table b (bId number
    , bId2 number
    , bValue varchar2(4));
insert into b values (1, 10, 'mno');
insert into b values (20, 20, 'pqr');
insert into b values (3, 1, 'stu');
commit;

Query:

select A.*
    , case when A.aId = B.bId then B.bId end as bId
    , case when A.aId2 = B.bID2 then B.bId2 end as bId2
    , case when A.aId = B.bId 
        and A.aId2 = B.bId2 then bValue end as bValue
from A
left outer join B on A.aID = B.bId or A.aId2 = B.bId2;

Results:

       AID       AID2 AVAL        BID       BID2 BVAL
---------- ---------- ---- ---------- ---------- ----
         1         10 abc           1         10 mno
         2         20 def  NULL               20 NULL
         3         30 ghi           3 NULL       NULL
         4         40 jkl  NULL       NULL       NULL

I don't think you are going to find an easy solution to this, here is something that works on your data set, but isn't pretty or efficient!

create table A ( aID int, aID2 int, avalue char(3) )
create table B ( bID int, bID2 int, bvalue char(3) )

insert into A VALUES (1  , 10  , 'abc')
insert into A VALUES (2  , 20  , 'def')
insert into A VALUES (3  , 30  , 'ghi')
insert into A VALUES (4  , 40  , 'jkl')


insert into B VALUES (1  , 10  , 'mno')
insert into B VALUES (20 , 20  , 'pqr')
insert into B VALUES (3  , 1   , 'stu')

select distinct
    A.*,
    COALESCE(B1.bID,B2.bID) as bID,
    COALESCE(B1.bID2,B3.bID2) as BID2,
    B1.bvalue
from A
left outer join 
    B B1
on 
    A.aID = B1.bID 
AND 
    A.aID2 = B1.bID2
left outer join 
    B B2
on 
    A.aID = B2.bID 
left outer join 
    B B3
on 
    A.aID2 = B3.bID2


aID, aID2, avalue, bID , bID2, bvalue
=====================================
1  , 10  , 'abc' , 1   , 10  , 'mno'  
2  , 20  , 'def' , NULL, 20  , NULL
3  , 30  , 'ghi' , 3   , NULL, NULL
4  , 40  , 'jkl' , NULL, NULL, NULL

Not quite self joins, but no better, i'd be interested in seeing a better solution and also understanding the requirement.

Not sure why you can't use/don't want self joins, but here's a version:

SELECT  a.aID,
    a.aID2,
    a.avalue,
    b1.bID,
    b2.bID2,
    CASE WHEN b1.bID = b2.bID AND b1.bID2 = b2.bID2 THEN b1.bvalue ELSE NULL END as bvalue
FROM A  a
LEFT OUTER JOIN B b1
    ON (a.aID = b1.bID) 
LEFT OUTER JOIN B b2
    ON (a.aID2 = b2.bID2)

Results:

aID aID2    avalue    bID     bID2     bvalue
1   10      abc         1      10       mno       
2   20      def         NULL   20       NULL
3   30      ghi         3      NULL     NULL
4   40      jkl         NULL   NULL     NULL

To make this easier to write (and therefore maintain), I suggest you avoid outer join and instead union the four subsets you require e.g.

SELECT A.*, B.* FROM A INNER JOIN B ON (A.aID = B.bID AND A.aID2 = B.bID2)
UNION
SELECT A.*, NULL, NULL, NULL
  FROM A 
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM B 
                    WHERE (A.aID = B.bID)
                  )
       AND NOT EXISTS (
                       SELECT * 
                         FROM B 
                        WHERE (A.aID2 = B.bID2)
                      )
UNION
SELECT A.*, B.bID, NULL, NULL
  FROM A INNER JOIN B ON (A.aID = B.bID)
       AND NOT EXISTS (
                       SELECT * 
                         FROM B 
                        WHERE (A.aID2 = B.bID2)
                      )
UNION
SELECT A.*, NULL, B.bID2, NULL
  FROM A INNER JOIN B ON (A.aID2 = B.bID2)
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM B 
                    WHERE (A.aID = B.bID)
                  );

The advantage to this approach is that is uses relational operators join, semi difference and union, allowing those non-relational NULL values (which outer join is expressly designed to generate) to be easily replaced with actual default values.