SQL Select - How do I do this?

[monster1977]

Hello

I'm having difficulty writing an SQL command required for my web
application.

I have two tables: WebItems and Costumes. WebItems contains each
instance of a fancy dress costume in my database. Costumes contains all
the general information on each costume.

I want to select all the WebItem records and link them to Costumes to
get further details.

WebItem has four fields: Id (primary key), CodeNumber, webcat_id (the
category the item is in) and sequence.

Costumes has lots of fields, including code (which links to
WebItem.CodeNumber) and Photo, which contains a file name for the
product image.

I basically need to get back only one row per unique Costumes.Photo.

For example, Costumes contains...

Name Photo
----------------------------------------------------------------
Robin Hood /images/robin.jpg
Robin Hood (L) /images/robin.jpg

I only want to display one option per photograph - how to I restrict my
select statement to achieve this?

I've tried linking the tables together and then using a GROUP BY on
Costumes.Photo:

SELECT Costumes.Photo
FROM Costumes RIGHT JOIN WebItem ON Costumes.Code = WebItem.codenumber
WHERE (((WebItem.webcat_id)=55) AND ((Costumes.Photo)<>""))
GROUP BY COstumes.Photo

But this method doesn't allow me to get any other required data, only
Costumes.Photo.

Any help will be greatly appreciated.

James Bird

[Gord]

>I've tried linking the tables together and then using a GROUP BY on

Costumes.Photo:

SELECT Costumes.Photo
FROM Costumes RIGHT JOIN WebItem ON Costumes.Code = WebItem.codenumber
WHERE (((WebItem.webcat_id)=55) AND ((Costumes.Photo)<>""))
GROUP BY COstumes.Photo

But this method doesn't allow me to get any other required data, only
Costumes.Photo.

Try using the First() function with other columns, as in

SELECT First(Costumes.whatever), Costumes.Photo ...
.... GROUP BY Costumes.Photo