SQL Server left outer join pass condition to sub request to join first line found

Question

create table #produit 
(
    id int IDENTITY(1,1) PRIMARY KEY,
    pk_article int,
    name_article varchar(max),
    pk_basket int
);

insert into #produit
       (pk_article, name_article, pk_basket) 
values (1, 'article 1', 214),
       (2, 'article 1', 214),
       (3, 'article 1', 214),
       (1, 'article 1', 215),
       (2, 'article 1', 215),
       (4, 'article 1', 216),
       (5, 'article 1', 216);

insert into #pictures
       (pk_article, url_picture) 
values (1, 'url1'),
       (1, 'url2'),
       (1, 'url3'),
       (2, 'url4'),
       (2, 'url5'),
       (3, 'url6'), 
       (4, 'url7'),
       (5, 'url8')

I can do a top to have one picture for a specific article :

select top 1 * 
from #pictures
where pk_article = 1
order by url_picture asc

I need a list of baskets paginated:

select * 
from 
    (select 
         dense_rank() over (order by pk_basket ASC) AS rang,
         *
     from #produit) as result
where rang >= 1 and rang <= 10

rang    id  pk_article  name_article    pk_basket
1       1   1           article 1       214
1       2   2           article 1       214
1       3   3           article 1       214
2       4   1           article 1       215
2       5   2           article 1       215
3       6   4           article 1       216
3       7   5           article 1       216

How can I add a join to add the picture request to have one picture per article?

Something like:

select * 
from 
    (select 
         dense_rank() over (order by pk_basket ASC) AS rang,
         *
     from 
         #produit 
     left outer join 
         (select top 1 * 
          from #pictures
          where pk_article = #produit.pk_article
          order by url_picture asc) as first_picture on first_picture.pk_article = #produit.pk_article
    ) as result
where
    rang >= 1 and rang <= 10

but I can't pass the #produit.pk_article to the sub request.

Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) — philipxy, Dec 22 '22 at 10:33
thanks for your response, but in my case, i don't have a date field or an id picture that i could compare. — Lucas Weibel, Dec 22 '22 at 11:42

score 0 · Answer 1 · answered Dec 22 '22 at 15:40

Solution using OUTER APPLY :

select * from (
    select 
        DENSE_RANK() OVER (ORDER BY pk_basket ASC) AS rang,
        *
    from #produit 
    OUTER apply ( select top 1 url_picture
                    from #pictures 
                   where #produit.pk_article = #pictures.pk_article ) as first_picture_article
    ) as result
WHERE rang >= 1  AND rang <= 10

SQL Server left outer join pass condition to sub request to join first line found

1 Answers1