0

I am trying to create a simple query with a inner lateral join but I want to restrict the join to a single result in the subquery

select b.`CODE`
from foo.bar.`BRANCH` b
inner join lateral (
   select branch_id
   from foo.bar.`BRANCH_DISTANCE`
   where branch_id=b.CODE
   and distance < 100
   limit 1
) on true

The BRANCH_DISTANCE table contains the distances between any two branches and I want to return all branches that are within 100 km of another branch, which is why in the subquery, as long as there is one record that contains the branch and its distance is less than 100, it should return the branch (and stop looking for any further matches).

But when I add the limit, the query returns only one record. On removing the limit, around 2000 records are returned.

If I replace the select b.CODE with select distinct b.CODE, the get around 500 results (which is the correct answer).

My objective is to not use the distinct keyword in the select statement and that is why I was adding the limit in the subquery so that the join is done not on every record in the BRANCH_DISTANCE table that contains the branch code and distance < 100 (because it is possible for a branch to be less than 100 km away from more than one branch).

thisisshantzz
  • 1,067
  • 3
  • 13
  • 33

1 Answers1

0

Join may multiply resulting rows count for the case when joining is happening on the column with duplicate values (in this one, or both of branch_id and b.CODE columns have duplicate values).

To restrict the join to a single result in a subquery, please use IN clause. So something like this should work as expected:

select b.`CODE`
from foo.bar.`BRANCH` b
where b.`CODE` in (
   select branch_id
   from foo.bar.`BRANCH_DISTANCE`
   and distance < 100
)
Vova Vysotskyi
  • 661
  • 4
  • 8