Hi Tim,

I wondering if it's possible to easily do this....

Here's 2 basic tables storing rooms and owners.

[ROOMS]
roomNo    ownerID
----------------------
A1             1
B1             2
C1             1


[OWNERS]
ownerID    name
-------------------------
1              Joe Bloggs
2              Bob Smith


What I want to do is list the owners in a drop down but also show the
rooms they own like this:

1 Joe Bloggs (A1,C1)
2 Bob Smith (B1)

So I was thinking of a little query that will return 3 fields, the
ownerID, name and rooms which would contain all related rooms in 1
field, almost like an array although access to the individual rooms in
this field is not required it's purely to display in the drop down so
the user can visually associate the rooms with a particular owner.

Is this easy to do?

Thanks,

Phil

Dear Phil,

What you are asking is to return a list of child-items into a single field in a result
table, separated by commas.

My understanding is that this is not possible in DBISAM at present. It is actually a
feature request I put to Tim et al about a year back.

It is moderately easy to do in Delphi ... and this is how I do it myself at present.

1. Run the Master query (returning Joe Bloggs, Jim Smith etc. once each), with multiple
rows returning the rooms per person.

2. Use a simple "while" loop to build a TStringList where each TStringlist.strings has the
form you want.

3. Set your lookup.items = to TStringlist.

... issues to remember:

- do you want to return people who have no rooms? If so you have to do your joins carefully.
- in the while loop use "dataset.next; while not dataset.EOF" type code

Hope this is useful.


adam


Phil Read <phil@vizualweb.com> wrote:

Hi Tim,

I wondering if it's possible to easily do this....

Here's 2 basic tables storing rooms and owners.

[ROOMS]
roomNo    ownerID
----------------------
A1             1
B1             2
C1             1


[OWNERS]
ownerID    name
-------------------------
1              Joe Bloggs
2              Bob Smith


What I want to do is list the owners in a drop down but also show the
rooms they own like this:

1 Joe Bloggs (A1,C1)
2 Bob Smith (B1)

So I was thinking of a little query that will return 3 fields, the
ownerID, name and rooms which would contain all related rooms in 1
field, almost like an array although access to the individual rooms in
this field is not required it's purely to display in the drop down so
the user can visually associate the rooms with a particular owner.

Is this easy to do?

Thanks,

Phil

"adam" <adam@nospamplease.fullwellmill.co.uk> wrote in message
news:DC2A9157-28B9-4862-8BC1-0C236F449993@news.elevatesoft.com...
>
> My understanding is that this is not possible in DBISAM at

select id, name, cast('' as char(30)) roomlist
into memory\temp
from owner;
update memory\temp a set roomlist = if(roomlist = '' then room else roomlist
+ ', ' + room)
from memory\temp
join rooms on rooms.id = a.id;
update memory\temp set roomlist = '(' + roomlist + ')';
select * from memory\temp;

Robert

Adam,

<< My understanding is that this is not possible in DBISAM at present. It is
actually a feature request I put to Tim et al about a year back. >>

Yes, we've had several requests for such an item - an aggregate "list
builder".

Perhaps I can include this as a "bonus" in the next build.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Robert,

<< select id, name, cast('' as char(30)) roomlist
into memory\temp
from owner;
update memory\temp a set roomlist = if(roomlist = '' then room else
roomlist
+ ', ' + room)
from memory\temp
join rooms on rooms.id = a.id;
update memory\temp set roomlist = '(' + roomlist + ')';
select * from memory\temp; >>

Nice query, that did the trick and thank you!

Cheers,

Phil.