In fact this should work too... select * from re_property prop, re_agent agent left outer join re_image image on image.imageid = (select max(imageId) from re_image where re_image.propid = prop.propid) where agent.propID = prop.propID
B) Brett Payne-Rhodes wrote: > Hi Seona, > > A workaround that should work... > > select * > from re_property prop, re_agent agent > left outer join re_image image > on image.propid = prop.propid > and image.imageid = (select max(imageId) from re_image where > re_image.propid = prop.propid) > where agent.propID = prop.propID > > Assuming 'imageId' is the key for your re_image table. > > This will get you the latest(?) image added for a property. min(imageId) > would get you the first(?) image added for a property. You might need a flag > on the image to indicate which one is the 'main' image perhaps... > > Cheers, > > Brett > B) > > > Seona Bellamy wrote: > >>Brett and Scott, >> >>Thanks for that, I think I'm slowly starting to understand the join thing. >> >>Sadly, however, I've found a small problem with the way we're doing >>this. Have a look at http://www.renovate.com.au/realestate/ to see what >>this is yielding me - when there's multiple images attached to a >>property, that property appears multiple times in the list. >> >>Now, I went and did a bit of reading and as far as I can tell that is >>exactly what's supposed to happen. It's not quite what I'd wanted to >>have happen, though. >> >>Is this a case where I simply have to abandon keeping the images in a >>seperate table and make them part of the re_property table? I'd always >>thought that was a clumsy way of doing it, but I'm willing to if it's >>the only way to achieve the results I want. >> >>Cheers, >> >>Seona. >> >> > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie -~----------~----~----~----~------~----~------~--~---
