SQL help please

Report · Aug 12, 2006

I have a record set that brings up a list of industrial Estates and checks with its estate reference in a table of corresponding units for units that are available i.e. not let and all works well.
My problem is some estates have more than one available unit and and therefore in my repeated region of the recordset I can have the same estate listed a few times over. I would like to show an estate in my repeated region only once even if it has a number of available units.
The code at the moment goes something like this:-

SELECT Region, Town, EstateName, Type, SizeSQFT, Estates.EstateReference
FROM Estates, Units
WHERE Region LIKE 'varRegion' AND Town LIKE 'varTown' AND EstateName LIKE 'varEstate' AND Type LIKE 'varType' AND SizeSQFT<= varSize AND Units.EstateReference = Estates.EstateReference AND Status ='Available'
ORDER BY Region, Town, EstateName

Report · Aug 12, 2006

On 12 Aug 2006 in macromedia.dreamweaver.appdev, eltucan wrote:

> I have a record set that brings up a list of industrial Estates and
> checks with its estate reference in a table of corresponding units
> for units that are available i.e. not let and all works well.

If you're getting the recordset you want, then the SQL is okay, and the
problem is not one of selection but of display. I gather that what you
want is something like:

Estate 1
Estate 1 Record 1
Estate 2
Estate 2 Record 1
Estate 2 Record 2
Estate 2 Record 3
Estate 3
Estate 3 Record 1
...

If that's the case, you can find what you want by looking for, in no
particular order:
- Nested recordset
- Horizontal looper (by, I think, Tom Muck)

Search for either of those terms, along with "Dreamweaver", and you
should find extensions which will help you get what you want.

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/email.php

Report · Aug 15, 2006

Thanks for your reply but it is not a nested recordset I am after really, but to be able to filter it. If the recordset is correct and I cannot see how else I could do it then I need to only show an estate once in the repeaed region even if it appears more than once