3

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, ...)

Jeykom
  • 31
  • 8
  • sry, i forgot these. postgresql 9.6 – Jeykom Sep 19 '18 at 15:04
  • I think Oracle 12c1+, PostgreSQL 9+, DB2 9+, SQL Server 2014+ will provide you a solution to this query. Which database and version do you have? – The Impaler Sep 19 '18 at 15:05
  • https://stackoverflow.com/questions/1124603/grouped-limit-in-postgresql-show-the-first-n-rows-for-each-group Refer this similar question – krithikaGopalakrishnan Sep 19 '18 at 16:16
  • https://stackoverflow.com/questions/1124603/grouped-limit-in-postgresql-show-the-first-n-rows-for-each-group is an other solution, but not that easy – Jeykom Sep 20 '18 at 08:50

1 Answers1

2

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.

The Impaler
  • 45,731
  • 9
  • 39
  • 76