How to conditionally use an inner join?-CodePudding

I'm working on a CTE (probably not relevant), and I have two possible inner joins, but the second one should only be used if the first one is empty (it could be used anyway, in fact, IT MUST be executed alongside the first inner join:

with recursive "test_table" as (
     select stuff from test_table 
          inner join base_table bt on bt.property = test_table.property // THIS MIGHT FIND A SET OF ROWS
          inner join base_table bt2 on bt2.id = test_table.innerProperty // THIS MIGHT FIND SOME OTHER ROWS
)

The thing is, this doesn't work, as it brings 0 rows, but if I execute the first inner join OR the second one separetely, it works just fine. I tried using an OR but it loops infinitely.

I'm using postgres 13, btw

CodePudding user response：

Assuming some sample data on your structures:

create table test_table (
  stuff text,
  property text,
  innerProperty text
);

create table base_table (
  property text,
  id text,
  stuff text
);

insert into test_table values
('foot1', 'foot', 'ball'),
('foot2', 'ultimate', 'frisbee'),
('foot3', 'base', 'ball'),
('foot4', 'nix', 'nein');

insert into base_table values
('foot', 'foot', 'feet'),
('flat', 'ball', 'pancake'),
('flit', 'frisbee', 'float');

I think in general using left outer joins instead of inner joins is the basis of the solution:

select t.stuff, bt.stuff, bt2.stuff
from
  test_table t 
  left join base_table bt on bt.property = t.property
  left join base_table bt2 on bt2.id = t.innerProperty
where
  bt.property is not null or bt2.id is not null

Where the where clause mimics basically what you are trying to do -- you want a match from one or the other.

However, this yields these results:

foot1   feet    pancake    <- Bt2.stuff should be empty if BT matches?
foot2           float
foot3           pancake

My understanding is that if there is a match on bt then you don't want to even evaluate the second join -- make bt2 conditional on NO match from bt. If that is the case you can simply change the join condition on bt2 from this:

left join base_table bt2 on bt2.id = t.innerProperty

to this:

left join base_table bt2 on bt2.id = t.innerProperty and bt.property is null

Meaning, don't do the BT2 join if BT was successful.

Which you can see skips removes bt2.stuff = pancake from the query:

foot1   feet    
foot2           float
foot3           pancake

Let me know if this is what you had in mind.

CodePudding user response：

INNER JOIN only keeps rows that meet the join condition on both sides, and in that sense it acts like a filter. When you have two inner joins, you are performing this filter twice, so when you get 0 rows returned it means that no row meets both of the 2 join conditions you used.

If I understand your question correctly, rather than do 2 inner joins, you should opt for 2 left joins and use COALESCE to set the condition that when the first join didn't produce any results, use the second join.

SELECT
    -- If bt.yourcolumn is NULL, 
    -- because the JOIN condition was not met, use b2.yourcolumn
    COALESCE(bt.yourcolumn, bt2.yourcolumn) AS yourcolumn
FROM ... --use left joins