[RESOLVED] SQL QUERY - What type of join do I need?

voidstate

I have two tables.

items (item_id, item_name )
images (image_id, item_id, filename, caption, display_order)

What I want is to return a data set containing each item once, together with the image which has the lowest display_order for that item.

For example, if the item with item_id value of 1 has three images related to it (ie. they all have 1 in the item_id column), I only want the one with the lowest value display_order to be included in the result set.

The data set should have the following columns: items.item_id, items.item_name, images.filename, images.caption.

Can you help? I'm completely stuck!

voidstate

Lars_Berg

select 
   item_name,
   filename, 
   caption
   from items left join images i1 on items.item_id = i1.item_id
    and display_order = 
   (select min(display_order)
      from images i2
    where  i1.item_id = i2.item_id)

voidstate

Hi Lars

Thanks for the reply.

Unfortunately this doesn't work. The result set still contains multiple images for each item.

Any other ideas?

voidstate

Actually, it does work!

I should have mentioned that items also has a display_order column, so it should have read:

SELECT dbo.items.item_name, i1.filename, i1.caption
FROM dbo.items LEFT OUTER JOIN
dbo.images AS i1 ON dbo.items.item_id = i1.item_id AND i1.display_order =
(SELECT MIN(display_order) AS min
FROM dbo.images AS i2
WHERE (i1.item_id = item_id))

Thank you so much!