By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

SQL Select - How do I do this?

P: n/a
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

May 22 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
>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

May 22 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.