How get data from three or more DB table without row duplicates

Using joint queries to get records from the DataBase(DB) withing the following code

    |> join(:left, [a], b in Table1, on: a.provider_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
           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

Sounds like you want distinct results.


An inner lateral join is another option. Something along the lines of:

 |> join(
      [a, b],
      c in fragment(
        SELECT *
        FROM  Table2 as c,
        WHERE  c.reference = ? 
        LIMIT  1
        field(a, :reference)
1 Like