CODEWITHSUNDEEP
sqloracle11gleft-join
user3807647
user3807647

Reputation: 1

Can you limit the results from a left join based on results in Oracle SQL?

Want to join table A to table B for specific types of rows in table B. If none of those types are in table B, then return the row in table A with null for columns in table B. Do not want null row, if a row with a type also returned.

My attempt to extrapolate the approach using RANK:

SELECT DISTINCT
     A.a_id, A.emp, B.b_id, B.id_for_c, C.type, C.rnk
 FROM A
 JOIN B on B.b_id = A.a_id
 Left JOIN ( SELECT b_id, C.type, id_for_c, C.c_id, rnk
      FROM ( SELECT B.b_id, B.id_for_c, C.type, C.c_id,
            RANK() OVER (PARTITION BY B.b_id
            ORDER BY CASE WHEN C.type IS NOT NULL THEN 1 ELSE 2 END) AS rnk
            FROM B
            LEFT OUTER JOIN C
            ON C.c_id = B.id_for_c
            AND (C.type=1 or C.type=2)
          ) C

      ORDER BY b_id, C.type, rnk) C
on C.c_id = B.id_for_c;   


Data:

create table a (a_id number, emp number);
create table b (b_id number, id_for_c number);
create table c (c_id number, type number);

insert into a values (101, 1000);
insert into a values (102, 2000);
insert into a values (103, 3000);
insert into a values (104, 4000);

insert into b values (101, 25);
insert into b values (101, 75);
insert into b values (102, 50);
insert into b values (103, 75);
insert into b values (104, 25);
insert into b values (104, 75);

insert into c values (25, 1);
insert into c values (50, 2);
insert into c values (75, 3);

Upvotes: 0

Views: 131

Answers (2)

Alex Poole
Alex Poole

Reputation: 191285

You can use an analytic rank() function to favour non-null B values of null ones:

SELECT id, type
FROM (
  SELECT A.id, B.type,
    RANK() OVER (PARTITION BY A.id
      ORDER BY CASE WHEN B.type IS NOT NULL THEN 1 ELSE 2 END) AS rnk
  FROM A
  LEFT OUTER JOIN B
  ON B.id = A.id_for_b
  AND (B.type=1 or b.type=2)
)
WHERE rnk = 1
ORDER BY id, type;

SQL Fiddle.

The inner query adds a ranking column which gives each matching result a ranking of either 1 or 2 (which you can see here. The outer query then eliminates those ranked 2; which will only be null values where a non-null value also exists.

As with Brian's answer, this only hits your real tables once.

If any A has multiple non-null values, i.e. with both types 1 and 2 (or no non-nulls, but multiple nulls) then they would all be shown; I'm not sure if either scenario is possible for you.

For your three-table variation I don't think you want to be joining to a subquery; certainly not back to the tables you've already worked from. Hopefully this gets the result you want:

SELECT a_id, b_id, id_for_c, type, c_id
FROM (
  SELECT A.a_id, A.emp, B.b_id, B.id_for_c, C.type, C.c_id,
    RANK() OVER (PARTITION BY B.b_id
    ORDER BY CASE WHEN C.type IS NOT NULL THEN 1 ELSE 2 END) AS rnk
  FROM A
  LEFT OUTER JOIN B
  ON B.b_id = A.a_id
  LEFT OUTER JOIN C
  ON C.c_id = B.id_for_c
  AND (C.type=1 or C.type=2)
)
WHERE rnk = 1
ORDER BY a_id, b_id;

| A_ID | B_ID | ID_FOR_C |   TYPE |   C_ID |
|------|------|----------|--------|--------|
|  101 |  101 |       25 |      1 |     25 |
|  102 |  102 |       50 |      2 |     50 |
|  103 |  103 |       75 | (null) | (null) |
|  104 |  104 |       25 |      1 |     25 |

SQL Fiddle.

Upvotes: 0

Brian DeMilia
Brian DeMilia

Reputation: 13248

Try:

with sub as
 (SELECT A.id, B.type
    FROM A
    LEFT OUTER JOIN B
      ON B.id = A.id_for_b
     and (B.type = 1 or b.type = 2))
select *
  from sub
 where not exists (select 1
          from sub x
         where x.id = sub.id
           and x.type in (1, 2)
           and sub.type is null)

Illustration:

http://sqlfiddle.com/#!4/174bd/1/0

(your existing query is the block in the with clause). I use an exists subquery to eliminate what you don't want.

Upvotes: 1

Related Questions