CODEWITHSUNDEEP
sqlpostgresql-9.6
Jeykom
Jeykom

Reputation: 31

SQL - LIMITing AND filtering a join at same time

I need a solution for following Problem. I have two Tables:

ids from new user (got by subquery)

+------------+
|  user_id   |
+------------+
| 1          | 
| 4          | 
| 5          |
+------------+

users (table with all users)
+------------+
|  user_id   |
+------------+
| 1          | 
| 2          | 
| 3          |
| 4          |
| 5          |
| ...        |
+------------+

i need to join this two tables. every new user needs exactly 3 connections to other users.

for example:

+----------+------+
| new_user | user |
+----------+------+
| 1        | 2    |
| 1        | 3    |
| 1        | 4    |
| 4        | 1    |
| 4        | 2    |
| 4        | 3    |
| 5        | 1    |
| 5        | 2    |
| 5        | 3    |
+----------+------+

the problem is to limit the entries to exactly 3 and to exclude redundant entries (like 1|1, 3|3, ...)

Upvotes: 3

Views: 290

Answers (1)

The Impaler
The Impaler

Reputation: 48770

In PostgreSQL you can use a lateral query to retrieve a limited number of rows in a subquery.

I don't know the exact structure of your main query or subquery but it should look like:

select t.*, ls.*
  from main_table t,
  lateral ( -- lateral subquery
    select * from secondary_table s
      where s.col1 = t.col2 -- filtering condition, if needed
      fetch first 3 rows only -- limit to a max of 3 rows
  ) ls;

The lateral subquery is executed once per every row in the main_table.

Upvotes: 2

Related Questions