- 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
Last activity: Feb 28, 2022, 09:54 PM