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!
Hi Karen,
Hmm... Well, I think you might be right about it not being the correct syntax - I tried it, cleared up the few discrepencies with names of things, and stopped getting errors. Sadly, I stopped getting anything else either - nothing is being returned.
I'd like to play with it a bit more, though, so I'm going to walk through it here and trust you to tell me if I'm understanding what it's doing correctly. :) I'm going to ask a lot of questions, so please bear with me as I try and work through this.
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
Okay, I changed the order of the top two bits in the FROM - the images are in reference to the property not the agent. Am I right in thinking that this means the joins should be between the property table and the image table? (since it's the propID that's joining them, and that doesn't even exist in the agent table)
So why do we join essentially the same information each time? Just to give it a different name? Why can't this be done through aliasing in the SELECT line?
WHERE a.propID = p.propID
Minor change... it's agentID that joins these two, but that's kinda immaterial for the sake of this problem.
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
Err... <> is "not equal to" , right? So we're saying only get it where none of the imageID's match? I think this is the bit that is stopping the query from returning anything. Despite the fact that you have given each one a diiferent name, I'm pretty sure it's still all pointing at the same data. So I think this means you'll never have a case hwere none of them match, because there all the same. Or have I missed something here?
I can definitely see the logic in what I think you're trying to do, I just don't think it's working in its current form. Damned if I know what form it needs to be in, though! *sigh*
Cheers,
Seona.
From: cfaussie@googlegroups.com [mailto:cfaussie@googlegroups.com] 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.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
