[cfaussie] Dealing with a random number of images

[Scott Thornton]

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

[cfaussie] Dealing with a random number of images

[Scott Thornton]

The SQL Access generates is not necessarily standard SQL. I feel it uses way 
too many brackets.

Bite the bullet. Type it in, and you will learn it!

>>> [EMAIL PROTECTED] 22/03/2006 3:28 pm >>>
On 22/03/06, Chad Renando <[EMAIL PROTECTED]> wrote:
>
> Just jumping in from left field here and may not be of help, but have
> you ever used MS Access?  I find it quite handy as a drag and drop SQL
> generator.  I tend to recreate all my databases in Access so I can
> hack and play and have it create the SQL for me.  I know Enterprise
> does the same, but I find Access a quick and dirty means to an end.
>
> Chad
> who spends most of his time in left field.


Interesting idea. I might have a play with that tomorrow if I haven't found
a solution by then. Too late to go setting up databases today. :)

Cheers,

Seona
who's a city girl at heart and tries to stay out of the fields - they tend
to be distressingly rural




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

[cfaussie] Dealing with a random number of images

[Scott Thornton]

Hi,

maybe you could ask over at macromedia exchange, there is a forum there 
exclusive to flash-forms within the coldfusion topic.

http://www.macromedia.com/cfusion/webforums/forum/categories.cfm?forumid=1&catid=22&entercat=y

I guess you need a sort of multiple detail section to display all of the 
photos, but only once for the property details.

I don't know much about these new fandangled forms, sorry.

>>> [EMAIL PROTECTED] 22/03/2006 2:01:38 pm >>>
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
-~--~~~~--~~--~--~---

[cfaussie] Dealing with a random number of images

[Scott Thornton]

Hello,

select
*
from
re_agent agent inner join
re_property prop on
   agent.agent_id = prop.agent_id left outer join
re_image image on
  prop.propid = image.propid

In practice, inner join first, then outer join, or there can be unintended 
consequences.

Once you use an inner join (or outer) you have to be consistent through-out the 
query.

These types of queries are faster than the antiquated where clause style. 
(particularly so if using MS SQL)

PS left outer joins can be performed with the old method. you use the =* syntax 
(or is it *=). I don't use it so I don't know for sure. 



Scott Thornton, Programmer
Application Development
Information Services and Telecommunications
Hunter-New England Area Health Service
p: +61 2 49813589
m: 0413800242
e: scott.thornton  hnehealth.nsw.gov.au

>>> [EMAIL PROTECTED] 22/03/2006 11:10:14 am >>>
On 22/03/06, Scott Thornton <[EMAIL PROTECTED]> wrote:
>
> Hello,
>
> If you wish to use one query only, and have to deal with 0-n images, then
> you will have to left outer join your Image table.
> eg
>
> select
> *
> from
> re_property prop left outer join
> re_image image on
> prop.propid = image.propid
>
>
Ah! I've never understood the whole outer/inner join bit, so I tend not to
use them. Probably why I tie my brain in knots trying to find an
alternative. :)

So if I then also wanted to get the agent data (which is easier since each
property must have 1 agent) would that involve another join of some sort, or
would it be like this:

select
*
from
re_property prop left outer join
re_image image on
prop.propid = image.propid,
re_agent agent
where
agent.propID = prop.propID

That's the way I've always handled such things in the past, but I'd like to
learn how to use joins properly if they're a better way of working it. :)

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

[cfaussie] Dealing with a random number of images

[Seona Bellamy]

Hi guys,

Putting together a real estate listing for our website, and thought
that this might be a good place to use a simple (relatively) Flash
Form. Good in theory, but causing problems in practise. The problem I
currently have is twofold:

PROBLEM 1:
Trying to get all the necessary data is doing my head in! Whenever I
have to work with all but the simplest relationships between two
tables, I just tie myself in knots. I currently have the following
basic structure in my database:
re_property (propID, agentID, propName, ...)
re_agent (agentID, agentName, ...)
re_image (imageID, propID, imageURL)

Each property comes under one agent, and can have between 0 and 5
images associated with it. I've tried using the ideas in some of my
other queries (which some of the clever folk on this list helped me
with a while back) but they don't seem to allow for the possibility of
the zero-relationship. I have one test property record with no
assocaited images, and all my attempts at a query have either omited it
from the recordset or associated it with the three images from the
other test property.

If anyone can set me on the right track with this, it would be greatly
appreciated. As far as I can tell, to use it in a binding between a
 and a details panel, I need all of the data to come out
of one query.

PROBLEM 2:
Once I get these elusive images, I want to display them in the details
panel. I've searched around quite a bit, but I can't seem to find what
I'm looking for. Ideally, I would prefer not to just display them all
marching down the page, but in a nice little thumbnail arrangement
where there is one big image and then a list of other images that, when
clicked on, will replace the current main image.

Did that sentence make any sense? Hope so. :) Anyway, if anyone
understands what I'm trying to do and has any suggestions about where I
could look for answers, that would be great.


Oh, also feel free to let me know if what I want to do isn't possible
with Flash Forms and I should be looking at working on my _javascript_
skills or something instead.

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