[cfaussie] Re: Dealing with a random number of images

[Karen Johnstone]

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     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
-~----------~----~----~----~------~----~------~--~---