CODEWITHSUNDEEP
sqlsql-serverinner-join
Richard Parnaby-King
Richard Parnaby-King

Reputation: 14862

How to join a table where the table name is stored as a column value in another table?

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]

Upvotes: 0

Views: 80

Answers (2)

Richard Parnaby-King
Richard Parnaby-King

Reputation: 14862

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can do what you want with a left join and coalesce():

SELECT . . .,
       COALESCE(pg1.col pg2.col, . . .) as col
FROM seller s LEFT JOIN
     payment_gateway pg1 
     ON s.payment_gateway_id = pg1.id AND s.code = '1' LEFT JOIN
     payment_gateway pg2 
     ON s.payment_gateway_id = pg2.id AND s.code = '2' LEFT JOIN
     . . .

A LEFT JOIN is probably the most efficient way of handling this data in a single query. A better data structure would have all the payment gateway information in a single table.

Upvotes: 1

Related Questions