Conditional on LEFT JOIN PostgreSQL

postgresql left join multiple tables
postgresql left join multiple conditions
postgresql left join with where clause
postgres left outer join not working
postgresql left outer join multiple tables
postgres join
postgres nested joins
in postgresql

I have 3 tables:

  • One has 'locations' information.
  • Other contains information on user preferred 'location' (from locations)
  • Other contains information on which location the user has visited.

I want to display the address information on where was the visit made, but if this is not available then use the address information of the user preferred location.

I'm having trouble on making the conditional for the left join (if one is not available then grab from the other table)

Here's my query:

SELECT rew.cust_id,
    rew.location_name,
    CASE WHEN rew.used_location IS NULL THEN customer.home_location ELSE rew.used_location END, 
    locations.street_address,
    locations.city,
    locations.zip_code FROM rew
INNER JOIN customer ON rew.cust_id = customer.cust_id
LEFT JOIN locations ON rew.used_location = locations.location_name
WHERE rew.cust_id = 12

THIS PRINTS THE FOLLOWING

cust_id    |   location_name   |   used_location    |   street_address   | city   |   zip_code
-----------------------------------------------------------------------------------------------
   12      |   place 1         |     place 1        |   123 My Street    | City   |     00000
   12      |   place 1         |     place 1        |   NULL             | NULL   |     NULL
   12      |   place 1         |     place 1        |   NULL             | NULL   |     NULL

On this query, the used_location for the 2nd and 3rd record is actually the conditional of customer.home_location but it's not connecting the information on the address. How can I make if null then put the address from customer.home_location on the JOIN for locations

I think you want to do the LEFT JOIN between customer and rew (in this direction, for each customer possibly get a visited location - alternatively a RIGHT JOIN between rew and customer), then afterwards join this against the locations table to get the information about the selected location.

The query would look like this:

SELECT customer.cust_id,
    locations.location_name,
    locations.street_address,
    locations.city,
    locations.zip_code
FROM customer
LEFT JOIN rew USING (cust_id)
INNER JOIN locations ON COALESCE(rew.used_location, customer.home_location) = locations.location_name
WHERE customer.cust_id = 12

Alternatively, a bit clearer with a nested query:

SELECT result.cust_id,
    locations.location_name,
    locations.street_address,
    locations.city,
    locations.zip_code
FROM (
    SELECT
        customer.cust_id,
        COALESCE(rew.used_location, customer.home_location) AS location_name
    FROM customer
    LEFT JOIN rew USING (cust_id)
    WHERE customer.cust_id = 12
) AS result
INNER JOIN locations USING (location_name)

Also I've simplified the CASE expression into a COALSCE call.

PostgreSQL LEFT JOIN, Introduction to PostgreSQL LEFT JOIN clause. First, specify the columns in both tables from which you want to select data in the SELECT clause. Second, specify the left table (table A ) in the FROM clause. Third, specify the right table (table B ) in the LEFT JOIN clause and the join condition after the ON keyword. LEFT OUTER JOIN. The LEFT OUTER JOIN will return all rows in the table on the left-hand side and only the rows in the right-hand side table where the join condition has been satisfied. Syntax: SELECT columns FROM table-1 LEFT OUTER JOIN table-2 ON table-1.column = table-2.column; We need to see the name of each book and the corresponding Price.

Try this:

SELECT rew.cust_id,
    rew.location_name,
    CASE WHEN rew.used_location IS NULL THEN customer.home_location ELSE rew.used_location END, 
    locations.street_address,
    locations.city,
    locations.zip_code FROM rew
INNER JOIN customer ON rew.cust_id = customer.cust_id
LEFT JOIN locations ON 
    rew.used_location = locations.location_name
    AND rew.used_location IS NOT NULL
    OR customer.home_location = locations.location_name
    AND rew.used_location IS NULL
WHERE rew.cust_id = 12

Conditional Join (or something along those lines), (I'm new at joins if you couldnt guess):. SELECT r.region_name, r.region_no, cj. relationship, c.name_last, c.name_first, c.email. FROM ((region r. LEFT JOIN PostgreSQL left join is also known as left outer join. It will fetch all the records from the left table and matching records from the right table. If the right table has null values then no matching record will take place. PostgreSQL left join is used to select data from multiple tables in a single query.

Your join for address is always on the rew.used_location column which by your definition may be NULL. You need to make the join conditional similar to how you made the third display column conditional.

SELECT rew.cust_id,
       CASE WHEN rew.used_location IS NULL THEN customer.home_location ELSE rew.used_location END AS location_to_show,
       rew.location_name,
       locations.street_address,
       locations.city,
       locations.zip_code FROM rew
INNER JOIN customer ON rew.cust_id = customer.cust_id
LEFT JOIN locations ON  
  CASE 
    WHEN rew.used_location IS NULL THEN customer.home_location
    ELSE rew.used_location
    END = locations.location_name

SQL Fiddle To Try This

PostgreSQL LEFT JOIN or LEFT OUTER JOIN, What is PostgreSQL Left Join or Left Outer Join? The PostgreSQL LEFT JOIN joins two tables and fetches rows based on a condition, which is� Introduction to PostgreSQL LEFT JOIN clause. First, specify the columns in both tables from which you want to select data in the SELECT clause. Second, specify the left table (table A ) in the FROM clause. Third, specify the right table (table B ) in the LEFT JOIN clause and the join condition after

If else statement on join query, What you need are Conditional Expressions. FROM professionals LEFT JOIN accounts ON accounts.account_id = professionals.account_id� Looking up values in a hash table only works if the operator in the join condition is =, so you need at least one join condition with that operator. Merge join strategy. In a merge join, PostgreSQL picks all join conditions with the = operator. It then sorts both tables by the join keys (which means that the data types must be sortable).

PostgreSQL conditional join and value distribution with materialized , COALESCE( D.impressions * S.spend / SUM(D.impressions) OVER( PARTITION BY D.date, D.pid) , D.spend ) FROM display D LEFT JOIN� Hello, I'm currently camping with a bit of a problem -- i have a common requests table, and based on an entry's type, I need to join a specific other table. Consider this database layout: ##### -- table where all common request data is stored CREATE TABLE log.requests ( id BIGSERIAL, type SMALLINT NOT NULL, timestamp INTEGER NOT NULL, -- all kinds of common data ) -- only rows where log

Understanding Tricky Joins and Multi-Table Updates in PostgreSQL , SELECT * FROM table_1 LEFT JOIN table_2 ON table_1.id = table_2.id; type there is no ON <expression> clause specifying a join condition. SQL standard defines three types of OUTER JOINs: LEFT, RIGHT, and FULL and PostgreSQL supports all of these. In case of LEFT OUTER JOIN, an inner join is performed first. Then, for each row in table T1 that does not satisfy the join condition with any row in table T2, a joined row is added with null values in columns of T2.

Comments
  • customer.home_location is the "preferred" one, and rew are the visits?
  • Thank you very much, I got the result I wanted! :)