SQL question

I have to write a SQL query but I'm not sure how to do it. EDIT: I HAVE UPLOADED A TEST TABLES IN MS ACCESS FORMAT I have two tables. The primary key is property_id in table property. The primary key in table photos is photos is photo_id. Table photos also has a foreign key property_id. TABLE PROPERTY property_id ok field2 etc. TABLE PHOTOS photo_id property_id thumbnail field2 The relationship is one to many between property and photos. I need to select a single photo for every property, with some restrictions on which property records I choose: property.ok=1 Also there is another restriction, some properties have a thumbnail photo chosen (photos.thumbnail=1). But some properties have no thumbnail. So if there is a thumbnail chosen, I would that photo returned. If there is no thumbnail chosen, the I would like the first photo, so perhaps order the photos table by thumbnail and choose the first photo for each unique property. WHERE IN is very inefficient, I believe. I would like to do this with a join. Please suggest a SELECT statement that accomplishes this with a join or some other efficient manner. I will probably choose more than one solution. I'm expecting to pay $10 or so for each solution. ## Deliverables 1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done. 2) Deliverables must be in ready-to-run condition, as follows (depending on the nature of the deliverables): a) For web sites or other server-side deliverables intended to only ever exist in one place in the Buyer's environment--Deliverables must be installed by the Seller in ready-to-run condition in the Buyer's environment. b) For all others including desktop software or software the buyer intends to distribute: A software installation package that will install the software in ready-to-run condition on the platform(s) specified in this bid request. 3) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder's Seller Legal Agreement). * * *This broadcast message was sent to all bidders on Sunday Mar 23, 2008 6:32:52 PM: I have uploaded two test tables in MS ACCESS format. Basically, there are 8 property records. There are 20 photos. Some properties have "ok"=1 some have "ok"=0. Some photos are thumbnails, some are not. ## Platform T-SQL