MySQL - LEFT JOIN with two fields

Question

I have two tables:

• invoices
• discount_vouchers

I the invoice table, there are two feilds:

• voucher_id
• author_voucher_ids

The voucher_id is the direct id of the discount_vouchers table.

The author_voucher_ids is a coma separated list of IDs from the discount_vouchers table.

Is it possible to LEFT JOIN the invoices and discount_vouchers tables with those two fields?

I have tried with:

LEFT JOIN discount_vouchers ON invoices.discount_voucher_id = discount_vouchers.id OR discount_vouchers.id IN (author_voucher_ids)

But this is extremely slow and I need to kill the process.

I know the table invoices is designed in a wrong way, but I am searching for a solution without having to touch the existing table structure.

Thank you.

Answer 1

You can use the FIND_IN_SET MySQL function:

LEFT JOIN discount_vouchers ON invoices.discount_voucher_id = discount_vouchers.id
OR FIND_IN_SET(discount_vouchers.id, author_voucher_ids) <> 0