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

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

Sounds like you want distinct results.

3 Likes

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

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