CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2008-r2
Outman
Outman

Reputation: 3320

SELECT TOP inside INNER JOIN

I created this simple database in SQL Server: 

create database product_test
go

use product_test
go

create table product 
(
    id int identity primary key,
    label varchar(255),
    description text,
    price money, 
);

create table picture 
(
    id int identity primary key, 
    p_path text, 
    product int foreign key references product(id) 
); 

insert into product 
values ('flip phone 100', 'back 2 the future stuff.', 950),
       ('flip phone 200', 's;g material', 1400)

insert into picture 
values ('1.jpg', 1), ('2.jpg', 1), ('3.jpg', 2)

What I want is to select all products and only one picture for each product. Any help is greatly appreciated.

Upvotes: 3

Views: 16736

Answers (5)

Hogan
Hogan

Reputation: 70523

I would use a windowing function like this:

SELECT *
FROM product 
JOIN (
  SELECT id, product, p_path, 
         row_number() OVER (PARTITION BY product ORDER BY id ASC) as RN
  FROM picture
) pic ON product.id = pic.product AND pic.RN = 1

As you can see here I am selecting the picture with the lowest id (ORDER BY id ASC) -- you can change this order by to your requirements.

Upvotes: 1

paparazzo
paparazzo

Reputation: 45096

just group by and take min or max
left join in case there is no picture 

select pr.ID, pr.label, pr.text, pr.price
     , min(pic.p_path)
from product pr 
left join picture pic 
on pic.product = pr.ID 
group by pr.ID, pr.label, pr.text, pr.price

Upvotes: -1

Backs
Backs

Reputation: 24903

SELECT 
*,
(
    SELECT TOP 1 p2.p_path
    FROM dbo.picture p2
    WHERE p.id = p2.product
) AS picture
FROM dbo.product p

Or with join:

SELECT 
*
FROM dbo.product p
INNER JOIN 
(
    SELECT p2.product, MIN(p2.p_path) AS p_path
    FROM dbo.picture p2
    GROUP BY p2.product
) AS pt
ON p.id = pt.product

But you need to change p_path to varchar type

Upvotes: 2

Mary
Mary

Reputation: 101

Have you tried using a correlated sub-query?

SELECT *, (SELECT TOP 1 p_path FROM picture WHERE product = p.id ORDER BY id) 
FROM picture p

Hope this helps,

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269703

I'm a fan of outer apply for this purpose:

select p.*, pi.id, pi.path
from product p outer apply
     (select top 1 pi.*
      from picture pi
      where pi.product = p.id
     ) pi;

You can include an order by to get one particular picture (say, the one with the lowest or highest id). Or, order by newid() to get a random one.

Upvotes: 7

Related Questions