Hi Seona,
Scott's idea to have the property's image
number (1-5) stored with image would reduce complexity of ensuring each joined
record is for a different image.
Did some testing & it looks like the
check to get unique images needs to go with the LEFT JOIN constraints as I
think Scott was suggesting.
Don't know how you'd do it without Scott's idea - you'd have to play around with it yourself.
My tables:
RE_Properties
(PropertyID, PropertyName,
AgentID)
RE_Agents (AgentID,
AgentName)
RE_PropertyImages
(ImageID, PropertyID, ListOrder, ImageName)
Query (same as
Scott's):
SELECT
p.PropertyName, a.AgentName,
i1.ImageName AS Image1,
i2.ImageName AS Image2,
i3.ImageName AS Image3,
i4.ImageName AS Image4,
i5.ImageName AS Image5
[...]
FROM RE_Properties
p
INNER JOIN RE_Agents
a
ON ( p.AgentID = a.AgentID
)
LEFT JOIN RE_PropertyImages
i1
ON ( p.PropertyID = i1.PropertyID AND
i1.ListOrder = 1 )
LEFT JOIN
RE_PropertyImages i2
ON ( p.PropertyID = i2.PropertyID AND
i2.ListOrder = 2 )
LEFT JOIN
RE_PropertyImages i3
ON ( p.PropertyID = i3.PropertyID AND
i3.ListOrder = 3 )
LEFT JOIN
RE_PropertyImages i4
ON ( p.PropertyID = i4.PropertyID AND
i4.ListOrder = 4 )
LEFT JOIN
RE_PropertyImages i5
ON ( p.PropertyID = i5.PropertyID AND
i5.ListOrder = 5 )
[WHERE
...]
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
---------------------------------------------------------------
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
