Sample Header Ad - 728x90

PRESTO Left join using multiple operators

0 votes
2 answers
3179 views
- I have 2 tables, one listing all our customers and then a 2nd listing every clients list of creditors/obligations - Firstly, I need to return all clients in t1 and then be able to determine if the client owns a vehicle or not by looking at t2 - This query seems to do the trick and works as expected
SELECT    t1.first_name,    t1.last_name,    t1.id_number,    CASE
          WHEN
             t2.uid IS NOT NULL 
          THEN
             1 
          ELSE
             0     END    AS vehicle_finance  FROM    db.client t1     LEFT JOIN
          db.obligation t2 
          ON t1.uid = t2.uid
          AND t2.creditor_name LIKE '%Vehicle Finance%'
- But recently I discovered additional variants/keywords to include for the creditor name and this is the updated query I came up with
SELECT
       t1.first_name,
       t1.last_name,
       t1.id_number,
       CASE
          WHEN
             t2.uid IS NOT NULL 
          THEN
             1 
          ELSE
             0 
       END
       AS vehicle_finance 
    FROM
       db.client t1 
       LEFT JOIN
          db.obligation t2 
          ON t1.uid = t2.uid
          AND lower(t2.creditor_name) LIKE '%vehicle finance%' 
          OR lower(t2.creditor_name) LIKE '%vehicle and asset finance%' 
          OR lower(t2.creditor_name) LIKE '%vehicle + asset finance%' 
          OR lower(t2.creditor_name) LIKE '%motor finance%'
- Can I use the operators(AND & OR) like this in a LEFT JOIN after ON? - I am not worried about duplicate rows being returned as the client will have several obligations, I have another process that takes care of the duplicates - Is this query correct and most efficient way of achieving this?
Asked by code-is-life (117 rep)
Feb 28, 2022, 06:05 PM
Last activity: Feb 28, 2022, 09:54 PM