Using joint queries to get records from the DataBase(DB) withing the following code
Transactions
|> join(:left, [a], b in Table1, on: a.provider_id == b.id)
|> join(:left, [a, b], c in Table2, on: c.reference == a.reference)
|> where([a, b, c], is_nil(a.deleted_at))
|> handle_filter(search_params)
|> select([a, b, c], %{
reference: a.reference,
transacting_user: c.username
provider_id: b.name,
redirect_url: a.redirect_url,
amount: a.amount,
})
|> order_by([desc: :created_at])
|> Repo.all()
But the about code is duplicating the DB records. one record appears twice and some appear four times
When i remove the code below, the duplicates stop
|> join(:left, [a, b], c in Table2, on: c.reference == a.reference)
But I need this table.
How can I successfully get data from the three tables with duplicate entries like the reference