filter one rs with another

[j_baldwin]

i am trying to filter one record set with another.

recordset 1:
SELECT merchant_inv.hotel_id, merchant_inv.name, merchant_inv.low_rate, merchant_inv.high_rate, merchant_inv.star_rating
FROM merchant_inv
WHERE merchant_inv.city LIKE 'New York%' and merchant_inv.state_province = 'NY' AND merchant_inv.high_rate <= '200' AND merchant_inv.star_rating = '3.0'

recordset 2:
SELECT images.thumbnail_url
FROM images
WHERE images.hotel_id='myHotelId'

myHotelId = $row_rsFeaturedHotels['hotel_id']

however, in my repeat region the filter only matches the first record in rs2 resulting in the same image being repeated for each record in rs1

[RichardOMarketing]

I too am trying to acheive something similar.

I am fairly new to this and am trying to understand your code.

In your varaible definition, (myHotelId = $row_rsFeaturedHotels['hotel_id']) how does the second recordset pick up the row specific 'hotel_id' from the dynamic results page from the first recordset?

Or are you showing the thumbnail images in the same page?

What I am trying to acheive is a second recordset result page based on the first recordset result page with a repeating table.

[Newsgroup_User]

j baldwin wrote:
> i am trying to filter one record set with another.

You need to join the two tables and run a single query:

SELECT merchant_inv.hotel_id, merchant_inv.name, merchant_inv.low_rate,
merchant_inv.high_rate, merchant_inv.star_rating, images.thumbnail_url
FROM merchant_inv, images
WHERE merchant_inv.city LIKE 'New York%' and merchant_inv.state_province =
'NY' AND merchant_inv.high_rate <= '200' AND merchant_inv.star_rating =
'3.0'
AND merchant_inv.hotel_id = images.hotel_id

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/

[j_baldwin]

there is a 1 to many relationship between the tables. using a join will return all the images for the hotels returned in the merchant_inv table. i am trying to exctract 1 image for each record.

[Newsgroup_User]

j baldwin wrote:
> there is a 1 to many relationship between the tables. using a join will return all the images for the hotels returned in the merchant_inv table. i am trying to exctract 1 image for each record.

Would SELECT DISTINCT achieve what you want? Otherwise you need to run a
separate query for every hotel selected in the first recordset.

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/