Join on three tables

Question

I am designing a dating app. I have two tables, match and customer, in match table I have these fields:

match_id(primary_key),
first_customer_id(foreign_key from customer_table),
second_customer_id (foreign_key from customer_table)

Now In response for front end I need to show all the:

matches(match_id,first_customer_id,first_customer_name,second_customer_id,second_customer_name)

I am able to perform a join between two tables: match and customer. But I want to perform join on match, first_customer & second_customer. Like in my case I need to perform join on two tables(Match & Customer) but with two foreign keys from same table(Customer)

For two tables I am using sqlalchemy and this query

select([
    Match,
    Customers
]).select_from(
    Match.join(Customers)
)  

Kindly help me thanks!

Answer 1

You probably need to use alias() for the second customers table.

In this example matches_t and customers_t are table objects.

with Session(engine) as session:
    second = customers_t.alias('second')
    joins = matches_t.join(customers_t, matches_t.c.first_customer_id == customers_t.c.id).join(second, matches_t.c.second_customer_id == second.c.id)
    q = select(matches_t, customers_t.c.name, second.c.name.label('second_name')).select_from(joins)
    rows = session.execute(q).fetchall()