Question 114837 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Replace JOIN OR

How can i modify join to get rid of JOIN AND (… OR …) conditions?

select
table1.id,
table1.country,
table1.city,
table1.platform,
sum(table2.installs) as installs
from 
  schema.table1  
  inner join schema.table2 on table1.id = table2.id 
  and (
    table1.country = table2.country 
    or table1.country is null
  ) 
  and (
    table1.city = table2.utm_source 
    or table1.utm_source is null
  ) 
  and (
    table1.platform = table2.platform  
    or table1.platform is null
  ) 
group by 
table1.id,
table1.country,
table1.city,
table1.platform,

These conditions: OR table1.platform IS NULL

Tags:

2

Answers


  1. 0

    You can use coalesce() and the row type to shorten your query:

    select table1.id, table1.country, table1.city, table1.platform,
       sum(table2.installs) as installs
  from schema.table1  
       join schema.table2 
         on (table1.id, 
             coalesce(table1.country, table2.country),
             coalesce(table1.city, table2.city),
             coalesce(table1.platform, table2.platform)
            ) = (table2.id, table2.country, table2.city, table2.platform)
 group by table1.id, table1.country, table1.city, table1.platform
;

    If you want to match the condition when country, city, or platform are null in both tables, then change the = to is not distinct from.

    Login or Signup to reply.
  2. 0 
    select
table1.id,
table1.country,
table1.city,
table1.platform,
(SELECT sum(table2.installs) from schema.table2 on table1.id = table2.id 
  and (
    table1.country = table2.country 
    or table1.country is null
  ) 
  and (
    table1.city = table2.utm_source 
    or table1.utm_source is null
  ) 
  and (
    table1.platform = table2.platform  
    or table1.platform is null
  ) 
)as installs
from 
  schema.table1

    I don’t know it’s work or not but it’s fine to try subquery on select to sum table2.installs

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search