CODEWITHSUNDEEP
postgresql
MCM13
MCM13

Reputation: 275

Conditional on LEFT JOIN PostgreSQL

I have 3 tables:

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

Upvotes: 0

Views: 72

Answers (3)

Bergi
Bergi

Reputation: 664630

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.

Upvotes: 1

Rudimentary
Rudimentary

Reputation: 81

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

Upvotes: 1

kasptom
kasptom

Reputation: 2458

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

Upvotes: 2

Related Questions