I have the following SQL query:
$sql = "
SELECT
id,
refid,
action_id,
action_type,
co_user_id,
cust_vend_id,
cust_vend_type,
Aes_decrypt(cust_vend_name, '".DBKEY."') AS cust_vend_name,
Aes_decrypt(amount, '".DBKEY."') AS amount,
Aes_decrypt(action_date, '".DBKEY."') AS action_date,
Aes_decrypt(memo, '".DBKEY."') AS memo,
Aes_decrypt(trans_id, '".DBKEY."') AS trans_id,
entry_datetime,
part_id,
polarity
FROM
generated_actions
WHERE acc_type = 1
AND acc_id = $ref_id
AND action_type != 2
AND reverse_id IS NULL
ORDER BY generated_actions.action_id DESC ";
It works, but I need to add an INNER JOIN ON either customers
or vendors
depending on whether the contents of cust_vend_type
column is a '1' (customers) or '2' (vendors) and then grab customers_comp_name
if '1' or `vendors_comp_name' if '2'.
Looking forward to choosing the best solution or marking up helpful advice!
UPDATE
This is the code updated with @ciaran's response below. The problem is that it seems to be returning an empty resultset even though I know there are qualified records.
$sql = "
SELECT
ga.id,
ga.refid,
ga.action_id,
ga.action_type,
ga.co_user_id,
ga.cust_vend_id,
ga.cust_vend_type,
Aes_decrypt(ga.amount, '".DBKEY."') AS amount,
Aes_decrypt(ga.action_date, '".DBKEY."') AS action_date,
Aes_decrypt(ga.memo, '".DBKEY."') AS memo,
Aes_decrypt(ga.trans_id, '".DBKEY."') AS trans_id,
ga.entry_datetime,
ga.part_id,
ga.polarity,
SELECT CASE
WHEN
ga.cust_vend_type IS NULL
THEN
NULL
WHEN
ga.cust_vend_type = '1'
THEN
AES_DECRYPT(c.cust_comp_name, '".DBKEY."')
ELSE
AES_DECRYPT(v.vendor_comp_name, '".DBKEY."') END AS cust_vend_name
FROM
generated_actions ga
LEFT OUTER JOIN
customers c
ON
c.cust_id = ga.cust_vend_id
LEFT OUTER JOIN
vendors v
ON
v.vendor_id = ga.cust_vend_id
WHERE
ga.acc_type = 1
AND
ga.acc_id = $ref_id
AND
ga.action_type != 2
AND
ga.reverse_id IS NULL
ORDER BY
ga.action_id DESC ";
Not sure what I've done wrong?