Always Get 1 record from Table 2 based on some condition

Question

I have two tables, Product and ProductImages.

Product table has 2 columns: ProductID and Name

ProductImages table has 4 columns: ID, ProductID, ImageName and Primary(bit).

The relation between Product and ProductImages is one to many, so one product can have many images, but for each product only one ProductImage will be Primary.

I need to write a query to get all products with their primary images. If the product does not have a primary image, 1st record for the ProductId should be fetched.

Sample product Table

| 1  | P1 |
| 2  | P2 |
| 3  | P3 |
| 4  | P4 |

Sample productImage Table

| 1   | 1 | P1-1 | 1
| 2   | 1 | P1-2 | 0
| 3   | 1 | P1-3 | 0
| 4   | 1 | P1-4 | 0
| 5   | 2 | P2-1 | 1
| 6   | 2 | P2-2 | 0
| 7   | 3 | P3-1 | 0
| 8   | 3 | P3-2 | 0
| 9   | 4 | P4-1 | 0
| 10  | 4 | P4-2 | 0

Output Table

| 1   | 1 | P1-1 | 1
| 5   | 2 | P2-1 | 1
| 7   | 3 | P3-1 | 0
| 9   | 4 | P4-1 | 0

I hope I clarified my question. Please ask if further clarification is required.

Answer 1

You can do this simply like this with row_number window function:

select * from Products p
join (select *, row_number()  
                 over(partition by ProductID order by ID) rn from ProductImages)pi 
      on p.ProductID = pi.ProductID and pi.rn = 1

I assume that primary image ID will precede non primary image IDs.

Answer 2

This is kind of "quick and dirty" but i works:

SELECT  pr.ProductID, pr.Name, prim.ImageName, 1 AS IsPrimary
FROM    @product pr
        INNER JOIN @productimage prim ON pr.ProductID = prim.ProductID
WHERE   prim.[Primary] = 1
UNION ALL
SELECT  pr.ProductID, pr.Name, prim.ImageName, 0 AS IsPrimary
FROM    @product pr
        INNER JOIN
        -- Get any image for this Product MIN, MAX,...what you want
        (
            SELECT  ProductID, MIN(ImageName) AS ImageName
            FROM    @productimage
            WHERE   [Primary] = 0
            GROUP BY ProductID

        ) prim ON pr.ProductID = prim.ProductID
        LEFT JOIN
        --Primary Images:
        (
            SELECT ProductID
            FROM @productimage pri
            WHERE pri.[Primary] = 1
        ) primages ON pr.ProductID = primages.ProductID
WHERE   primages.ProductID IS NULL --there is no primary image

The first query is for all products who have a primary image, the second query is for those who don't have that.

Answer 3

with fewer joins this looks neat and does the job

select a.ProductId, ProductName, ImageName, b.ID as ImageID, b.[Primary] , b.[Primary] as IsPrimary
into a
from tProduct a 
inner join tProductImages b on a.ProductID = b.ProductID 
where b.[Primary] = 1

;WITH cte AS
(
   SELECT a.ProductId, ProductName, ImageName, b.ID as ImageID, b.[Primary] as IsPrimary ,
         ROW_NUMBER() OVER (PARTITION BY b.ProductId ORDER BY b.ID) AS rn
   from tProduct a 
    inner join tProductImages b on a.ProductID = b.ProductID 
    where b.[Primary] = 0 and a.ProductID not in (select ProductId from a)
)
SELECT ImageID, ProductId, ProductName, ImageName, IsPrimary
FROM cte WHERE rn = 1
union
select  ImageID, ProductId, ProductName, ImageName, IsPrimary
from a

drop table a

For your reference

EDIT:

I was just going through it again until i realized no need for union query, Only below would be enough

;WITH cte AS
(
    SELECT a.ProductId, ProductName, ImageName, b.ID as ImageID, b.[Primary] as IsPrimary ,
         ROW_NUMBER() OVER (PARTITION BY b.ProductId ORDER BY b.[Primary] desc, b.ID) AS rn
    from tProduct a 
    inner join tProductImages b on a.ProductID = b.ProductID 
)
select * from cte where rn = 1

Answer 4

Decided to try this solution with joins and subqueries: FIDDLE

SELECT * FROM(
SELECT pi.id as ImageID ,p.id as ProductID,
pi.imagename as ImageName ,pi.primarybit as Primary_
FROM product p
JOIN
(select id, imagename,productid,primarybit
 FROM productimage 
 where primarybit = 1) pi
ON p.id = pi.productid
UNION
SELECT pi.id as ImageID ,p.id as ProductID,
pi.imagename as ImageName ,pi.primarybit as Primary_
FROM product p
JOIN productimage pi
ON p.id = pi.productid
WHERE not p.id in(
  select prod.id
  from product prod
  join productimage prodimg
  on prod.id = prodimg.productid
  where prodimg.primarybit = 1)
AND pi.id IN (
  select min(prodimg.id)
  FROM product prod
  join productimage prodimg
  on prod.id = prodimg.productid
  group by prodimg.productid
)) magictable
ORDER BY ImageID

Answer 5

Simple join with cross apply should do it

Select p.*, i.* 
from Product p 
inner join ProductImage i on p.ProductId = i.ProductId
cross apply(Select 
             ProductId, 
             MinProId = MIN(ProductImageId), 
             PrimaryProductId = MIN(case when IsPrimary=1 then ProductImageId else null end)  
           from ProductImage i  
           where i.ProductId = p.ProductId 
           group by i.ProductId )s
 where i.ProductImageId= isnull(s.PrimaryProductId, s.MinProId)