Hello,
Could you insert an integer column in your images table to represent the order
of the images per property?
That way you could join like
select
i1.imagename,
i2.imagename,
i3.imagename ..
from
re_property p inner join
re_agent a on
p.prop_id = a.prop_id LEFT OUTER JOIN
re_image i1 ON
i1.propid = p.propid and
i1.image_order = 1 LEFT OUTER JOIN
re_image i2 ON
i2.propid = p.propid and
i2.image_order = 2 LEFT OUTER JOIN
re_image i3 ON
i3.propid = p.propid and
i3.image_order = 3 etc etc etc
Scott Thornton, Programmer
Application Development
Information Services and Telecommunications
Hunter-New England Area Health Service
p: +61 2 49813589
m: 0413800242
e: scott.thornton <at> hnehealth.nsw.gov.au
>>> [EMAIL PROTECTED] 23/03/2006 10:01 am >>>
Hi Seona,
To have all images returned as a column value for the property, I think
you'd have to LEFT JOIN the image table N times, and make sure that each
joined record was for a different image. Something like below (probably not
correct syntax - just wanted to give you the idea). Might require too much
processing tho!
SELECT ...
FROM re_property p, re_agent a
LEFT OUTER JOIN re_image i1 ON i1.propid = p.propid
LEFT OUTER JOIN re_image i2 ON i2.propid = p.propid
LEFT OUTER JOIN re_image i3 ON i3.propid = p.propid
LEFT OUTER JOIN re_image i4 ON i4.propid = p.propid
LEFT OUTER JOIN re_image i5 ON i5.propid = p.propid
WHERE a.propID = p.propID
AND i1.imageID <> i2.imageID
AND i1.imageID <> i3.imageID
AND i1.imageID <> i4.imageID
AND i1.imageID <> i5.imageID
AND i2.imageID <> i3.imageID
AND i2.imageID <> i4.imageID
AND i2.imageID <> i5.imageID
AND i3.imageID <> i4.imageID
AND i3.imageID <> i5.imageID
AND i4.imageID <> i5.imageID
Thanks,
kj
--
---------------------------------------------------------------
--
Karen Johnstone - Software Development Manager
Web Raven Pty Ltd
Email: [EMAIL PROTECTED] Phone: +61 7 3220 2229
Web: http://www.webraven.com.au <http://www.webraven.com.au/> Fax: +61
7 3220 2280
---------------------------------------------------------------
_____
From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf
Of Seona Bellamy
Sent: Thursday, 23 March 2006 8:46 AM
To: cfaussie@googlegroups.com
Subject: [cfaussie] Re: Dealing with a random number of images
...
So can anyone else come up with a way I can write this query so that I get
the following criteria filled?:
* Each property appears in the recordset, regardless of whether or not it
has images
* Each property knows the details for it's agent, referenced by agentID
* Properties with more than one image will appear only once in the
recordset, but will know about all of their images.
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
-~----------~----~----~----~------~----~------~--~---
