Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 5 of 5 total |
Compiling several results into 1 field of a query. |
Wed, Oct 3 2007 1:02 AM | Permanent Link |
Phil Read | 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 |
Thu, Oct 4 2007 5:07 AM | Permanent Link |
adam | 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 |
Thu, Oct 4 2007 6:37 AM | Permanent Link |
"Robert" | "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 |
Thu, Oct 4 2007 11:28 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, Oct 4 2007 7:10 PM | Permanent Link |
Phil Read | 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. |
This web page was last updated on Saturday, May 4, 2024 at 09:18 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |