To allow multiple payment gateways in my system I have a table of defined payment gateways (id, name, code) where code is the table name (for example 'paypal') containing a specific payment gateway transaction responses.
In my sql server query I want to join the transaction table for each gateway based on the value of this column.
Is this possible? If so, how?
In my query so far I am joining the payment gateway table based on the id of the chosen payment gateway for the specific seller (where [s] is the seller table):
INNER JOIN [payment_gateway] AS [pg] ON [s].[payment_gateway_id] = [pg].[id]
What I want to do is something like:
INNER JOIN {{[pg].[code]}} AS [payment_table] ON [payment_table].[order_id] = [order].[id]
As the join is dependant on the column value, I have decided to do a LEFT OUTER JOIN on the table:
LEFT OUTER JOIN [paypal] as [pp] on [pp].[quote_id] = [q].[id] and [pg].[code] = 'paypal'
It means I'll need to add this line for every new payment gateway that I integrate, but I'm ok with that.