Mysql find best query

[asmith]

Hello

 

I have different kind of transactions on the site and their log is recorded in 3 tables: (with different column names)

 

log_a

log_b

log_c

 

Lets say each transaction is for paying a product (id_product). So no matter what kind the transaction is, it is finally gonna point to id_product.

I have another table:

 

paid_products

 

which is pointing that each product is done by which type of transaction:

 

id    transaction_type  transaction_id

3      log_a                    23

41    log_c                    912

 

I want to select all recent id_product which is connected to one of these log tables but have other columns data too.

 

I'm not sure if there is something like:

IF transaction_type = 'log_a'  then inner join log_a on ... OR

IF transaction_type = 'log_b'  then inner join log_b on ... OR

 

So I thought about converting paid_products table to something similar to this:

id    id_log_a    id_log_b    id_log_c

 

 

then run such query:

select *, COALESCE(different field names on log tables) as option1, COALESCE(different field names on log tables) as option2

from paid_products

left join log_a as a on  id_log_a = a.ID_LOG

left join log_b as b on  id_log_b = b.ID_LOG

left join log_c as c on  id_log_c = c.ID_LOG

 

Is there an obvious solution which I'm missing?

Any better idea?

Thanks for your time

[kickstart]

Hi

 

Think it would probably be best to union 3 queries together, one against each table. But alias the column names so that they are consistant between the 3 queries.

 

All the best

 

Keith

[agrant]

I agree with kickstart, union probably is the way to go. Typically the less complex the SQL the faster it will run.