data with a one-to-many relationship and limiting results

[againstTheWind]

I have a list of pictures associated with hotels. Each hotel (specified by a unique ID number) has several pictures associated with it. I would like to write a SELECT statement that will only return one photo from each hotel instead of all of the pictures.

the data looks like this:

HotelID pictureFilename
53 nicehotel.jpg
53 nicehote2.jpg
53 nicehote3.jpg

34 beachresort5.jpg
34 beachresort6.jpg

and the output that I want:

HotelID picturefilename
53 nicehotel1.jpg
34 beachresort5.jpg
etc...

thanks a bunch!!!

Sean

 

[gmmastros]

The question is... which picture?

Here's one way.

Select HotelId,
       Min(pictureFileName) As pictureFilename
From   TableName
Group By HotelId

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

select hotelid,MIN(pictureFilename) as picturefilename
from YourTable
group by hotelid

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[againstTheWind]

Thanks guys!

How would I perform the same function but select exactly 3 photos for each hotel?

Sean

 

[SQLSister]

What I would do is havea field in the pictures table which specifies the ordinal of the picture. So then you use a derived table to pick the top three ordinals for each hotel and put each one in the fiield for the specific picture. Clear as mud, right? Here's an example

select h.field1, h.field2, a.pic1, a.pic2, a.pic3 from hotel h
left join (select hotelid, case when picorder = 1 then picture end as pic1, 
case when picorder = 2 then picture end as pic2, case when picorder = 3 then picture end  as pic3 from hotelpics group by hotelid) a 
on h.hotelid = a.hotelid

Then make sure that the hotelid and the picorder have a unique index so that no two photos can be marked with the same order number. (You will of course have to change the insert picture process to populate the field.)

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[againstTheWind]

How would I accomplish the original selection of only one picture per hotel but with a join to another table?

For example add fields from another table such as HotelType(beach resort/mountain resort)etc...

I keep getting an error that one of the fields is not part of an aggregate function

 

[SQLSister]

That error ususally means you have to add the field to the group by clause

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[tperri]

You can also put "order by newid()" to get a random picture to come up, instead of selecting the MIN(id).

 

[againstTheWind]

SQLDenis' solution worked but I cannot get that to join with another table.

I am trying to use this code:


select hotelid,MIN(pictureFilename) as picturefilename
from YourTable
group by hotelid

and implement a join between the table used above as well as a Hotels table with a field that stores HotelType(beach, island, mountain) as well as HotelActive(true/false)

I want to display one picture for each hotel in a certain category(ie beach) and is also active(HotelActive=true)

I keep getting errors when I try to write a join statement.

Thanks!

 

[againstTheWind]

I forgot to mention that the tables will be joined by a unique HotelID field.

 

[jbenson001]

What is your Join statment and what is the error?