Question 286460 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Fetch N elements from table while joining other table with one-to-many relationship

I have the following data:

collection:

id label created_at
1 Collection of CVs #1 2023-01-01
2 Collection of business cards #1 2023-02-01
3 Collection of CVs #2 2023-03-01

model:

id label type format
1 CVs cv 21×29.7
2 Business cards business card 8.5×5.5

document:

id label collection_id model_id
1 John Doe CV 1 1
2 John Dorian CV 1 1
3 John Travolta CV 3 1
4 John Malkovich business card 2 2

A collection contains multiple documents, and a document has one model.

How do I fetch only collections containing CVs (along with the CVs in question) while limiting the number of collections returned?

(Collections’ labels are only here for clarity, the only way to know if a document is a CV is through its model)

With the following query:

select *
from collection
join document on document.collection_id = collection.id
join model on document.model_id = model.id
where model.type = 'cv'
fetch first 2 rows only;

I get the following result, which obviously gives me the first two rows:

id label created_at id label collection_id model_id id label type format
1 Collection of CVs #1 2023-01-01 1 John Doe CV 1 1 1 CVs cv 21×29.7
1 Collection of CVs #1 2023-01-01 2 John Dorian CV 1 1 1 CVs cv 21×29.7

What I want instead is the first two collections containing CVs, along with the CVs in question:

id label created_at id label collection_id model_id id label type format
1 Collection of CVs #1 2023-01-01 1 John Doe CV 1 1 1 CVs cv 21×29.7
1 Collection of CVs #1 2023-01-01 2 John Dorian CV 1 1 1 CVs cv 21×29.7
3 Collection of CVs #2 2023-03-01 3 John Travolta CV 3 1 1 CVs cv 21×29.7

SQL Fiddle: http://sqlfiddle.com/#!17/30750

Tags:

2

Answers


  1. 0

    edit based on your comment:

    select c.*, d.*, m.*
from document d
inner join collection c on c.id = d.collection_id
inner join model m on m.id = d.model_id and m.type = 'cv'

    it will return every collections and documents with model type ‘cv’

    Login or Signup to reply.
  2. 0

    We can use window function dense_rank() to limit number of collection that should be selected

    select *
from (
  select *, dense_rank() over (order by c.id) as rnk
  from collection c
  join document d on d.collection_id = c.id
  join model m on m.id = d.model_id and m.type = 'cv'
) as s
where rnk <= 2

    Here, you get results with less than two distinct collection_id. But possibly more than one per collection_id.

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search