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

Postgresql – Using a aliased column in the JOIN predicate

I have the following tables:

customers table:

name email
saqib [email protected]
scott [email protected]
amy [email protected]

account_description table:

account_type description
13 personal
14 corporate

Is there way to re-write the following query without CTE or subquery?

with cte_customers as (
  select 
    case
      when email ilike '%gmail.com' or email ilike '%hotmail.com' or email ilike '%yahoo.com' then 13
      else 14
    end as account_type_generated
    , name 
  from customers
)
select * 
from cte_customers
inner join account_description on account_type_generated = account_description.account_type  ;

SQL Fiddle:
https://www.db-fiddle.com/f/c5KCj5sCcJXmYxsHDCNGAF/0

Tags:

2

Answers


  1. 0

    Simply add the join and case WHen to the SELECT that you have in the CTE

    Query #1

    with cte_customers as (
  select 
    case
      when email ilike '%gmail.com' or email ilike '%hotmail.com' or email ilike '%yahoo.com' then 13
      else 14
    end as account_type_generated
    , name 
  from customers
)
select * 
from cte_customers
inner join account_description on account_type_generated = account_description.account_type  ;
    account_type_generated name account_type description
    13 saqib 13 personal
    14 scott 14 corporate
    14 amy 14 corporate

    Query #2

    select 
    case
      when email ilike '%gmail.com' or email ilike '%hotmail.com' or email ilike '%yahoo.com' then 13
      else 14
    end as account_type_generated
    , name 
    ,account_description.*
  from customers
  inner join account_description on     (case
      when email ilike '%gmail.com' or email ilike '%hotmail.com' or email ilike '%yahoo.com' then 13
      else 14
    end) 
  = account_description.account_type;
    account_type_generated name account_type description
    13 saqib 13 personal
    14 scott 14 corporate
    14 amy 14 corporate

    View on DB Fiddle

    Login or Signup to reply.
  2. 0

    just put the CASE into the ON:

    select 
    case
      when email ilike '%gmail.com' or email ilike '%hotmail.com' or email ilike '%yahoo.com' then 13
      else 14
    end as account_type_generated
    , name 
    ,ad.*
  from customers as c
  inner join account_description as ad
    on case
      when email ilike '%gmail.com' or email ilike '%hotmail.com' or email ilike '%yahoo.com' then 13
      else 14
    end = ad.account_type  ;

    enter image description here

    but then given the account_type_generated is duplicate, it could be drop:

    select 
    c.name 
    ,ad.*
from customers as c
join account_description as ad
    on case
      when c.email ilike '%gmail.com' or c.email ilike '%hotmail.com' or c.email ilike '%yahoo.com' then 13
      else 14
    end = ad.account_type  ;

    and the CASE could be rewritten as an IFF

    select 
    c.name 
    ,ad.*
from customers as c
join account_description as ad
    on IFF( c.email ilike '%gmail.com' or c.email ilike '%hotmail.com' or c.email ilike '%yahoo.com', 13, 14) = ad.account_type  ;

    This is all on Snowflake.

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

Back To Top
Search