p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Classic ASP Databases (http://p2p.wrox.com/forumdisplay.php?f=62)
-   -   selecting multiple records as one record array (http://p2p.wrox.com/showthread.php?t=9178)

Greywacke February 10th, 2004 05:52 AM

selecting multiple records as one record array
 
okay - here are my two tables:

Code:

TABLE: mmorpg
lng_id    str_name    str_currency
1    The Sims Online    Simoleans
2    Ultima Online    Gold
3    Shadow Bane    Gold
4    Ever Quest    Platinum
5    Eve Online    Isk
6    Earth & Beyond    Credits
7    Dark Age Of...    Gold
8    Anarchy Online    Credits

Code:

TABLE: servers
lng_id    lng_rpgid    str_name
1    1    Alphaville
2    1    Blazing Falls
3    1    Calvin's Creek
4    1    Dan's Grove
5    1    East Jerome
6    1    Fancey Fields
7    1    Interhogan
8    1    Jolly Pines
9    1    Mount Fuji
10    1    Test Center
11    2    AOL Legends
12    2    Arirang
13    2    Asuka
14    2    Atlantic
15    2    Balhae
16    2    Baja
17    2    Catskills
18    2    Chesapeake
19    2    Drachenfels
20    2    Europa
21    2    Formosa
22    2    Great Lakes
23    2    Hokuto
24    2    Izumo
25    2    Lake Superior
26    2    Napa Valley
27    2    Oceania
28    2    Pacific
29    2    Sonoma
30    2    Wakoku
31    2    Yamato
32    3    Carnage
33    3    Chaos
34    3    Corruption
35    3    Death
36    3    Deception
37    3    Dread
38    3    Fear
39    3    Mourning
40    3    Scorn
41    3    Test
42    3    Treachery
43    3    Vengeance
44    3    War
45    4    Rallos Zek
46    4    Xegony
47    4    The Rathe
48    4    Fennin Ro
49    4    Tarew Marr
50    4    Povar
51    4    E'ci
52    4    Veeshan
53    4    Solusek Ro
54    4    Cazic-Thule
55    4    Mithaniel Marr
56    4    Karana
57    4    Innoruuk
58    4    Tunare
59    4    Bertoxxulous
60    4    Rodcet Nife
61    4    Erollisi Marr
62    4    Prexus
63    4    Quellious
64    4    Brell Serilis
65    4    Tallon Zek
66    4    Vallon Zek
67    4    Bristlebane
68    4    The Tribunal
69    4    The Nameless
70    4    Lanys T'Vyl
71    4    Luclin
72    4    Druzzil Ro
73    4    Torvonnilous
74    4    Morell-Thule
75    4    Saryrn
76    4    The Seventh Hammer
77    4    Terris-Thule
78    4    Drinal
79    4    Tholuxe Paells
80    4    Xev
81    4    Ayonae Ro
82    4    Vazaelle
83    4    Zebuxoruk
84    4    Sullon Zek
85    4    Firiona Vie
86    4    Antonius Bayle
87    4    Stormhammer
88    4    Kane Bayle
89    5    Official Server
90    6    Official Server
91    7    Pendragon
92    7    Bedevere
93    7    Nimue
94    7    Palomides
95    7    Igraine
96    7    Iseult
97    7    Pellinor
98    7    Bors
99    7    Gawaine
100    7    Gaheris
101    7    Tristan
102    7    Kay
103    7    Galahad
104    7    Mordred
105    7    Percival
106    7    Lancelot
107    7    Guinevere
108    7    Merlin
109    7    Morgan
110    8    Atlantean
111    8    Rimor
112    8    Die Neue Welt
114    2    Baekdu
115    2    Mizuho
116    2    Siege Perilous
117    2    Lake Austin
118    2    Mugen
119    2    Sakura
120    4    Al'Kabor
121    4    Maelin Starpyre
122    4    Stromm
123    4    Test Server

i need to return a recordset which looks about like the following:

Code:

lng_id    str_name    str_currency    lng_srvid    str_name
1    The Sims Online    Simoleans    1, 2, 3, 4, 5, 6, 7, 8, 9, 10    Alphaville, Blazing Falls, Calvin's Creek, Dan's Grove, East Jerome, Fancey Fields, Interhogan, Jolly Pines, Mount Fuji, Test Center
2    Ultima Online    Gold    11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 114, 115, 116, 117, 118, 119    AOL Legends, Arirang, Asuka, Atlantic, Balhae, Baja, Catskills, Chesapeake, Drachenfels, Europa, Formosa, Great Lakes, Hokuto, Izumo, Lake Superior, Napa Valley, Oceania, Pacific, Sonoma, Wakoku, Yamato, Baekdu, Mizuho, Siege Perilous, Lake Austin, Mugen, Sakura
3    Shadow Bane    Gold    32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44    Carnage, Chaos, Corruption, Death, Deception, Dread, Fear, Mourning, Scorn, Test, Treachery, Vengeance, War
4    Ever Quest    Platinum    45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 120, 121, 122, 123    Rallos Zek, Xegony, The Rathe, Fennin Ro, Tarew Marr, Povar, E'ci, Veeshan, Solusek Ro, Cazic-Thule, Mithaniel Marr, Karana, Innoruuk, Tunare, Bertoxxulous, Rodcet Nife, Erollisi Marr, Prexus, Quellious, Brell Serilis, Tallon Zek, Vallon Zek, Bristlebane, The Tribunal, The Nameless, Lanys T'Vyl, Luclin, Druzzil Ro, Torvonnilous, Morell-Thule, Saryrn, The Seventh Hammer, Terris-Thule, Drinal, Tholuxe Paells, Xev, Ayonae Ro, Vazaelle, Zebuxoruk, Sullon Zek, Firiona Vie, Antonius Bayle, Stormhammer, Kane Bayl, Al'Kabor, Maelin Starpyre, Stromm, Test Server
5    Eve Online    Isk    89    Official Server
6    Earth & Beyond    Credits    90    Official Server
7    Dark Age Of...    Gold    91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109    Pendragon, Bedevere, Nimue, Palomides, Igraine, Iseult, Pellinor, Bors, Gawaine, Gaheris, Tristan, Kay, Galahad, Mordred, Percival, Lancelot, Guinevere, Merlin, Morgan
8    Anarchy Online    Credits    110, 111, 112    Atlantean, Rimor, Die Neue Welt

i would like to know can it be done, and if it can how the hell would i do it?

i think the sql str for adCmdText with ADODB.Recordset.Open would look somewhat like the following if it is possible:

Code:

SELECT DISTINCT mmorpg.str_name, STRJOIN(servers.str_name,', ') AS str_srvname, mmorpg.str_currency, STRJOIN(credits.lng_usercredits,', ') FROM mmorpg, servers, credits WHERE servers.lng_rpgid = mmorpg.lng_id AND servers.lng_id = credits.lng_lng_rpgid AND servers.lng_id = credits.lng_serverid ORDER BY servers.str_name ASC, mmorpg.str_name ASC
thing is, i have no idea how to select a column as an array, by distinct id's in a recordset...

any swift help asap would be greatly appreciated.

sincerely,
Pierre

Greywacke February 10th, 2004 10:33 AM

oki i have worked in access 2002 (XP) with the query editor to return sortof what i want...

Code:

SELECT DISTINCT mmorpg.str_currency, mmorpg.str_name, servers.str_name, credits.lng_usercredits
FROM credits INNER JOIN (servers INNER JOIN mmorpg ON servers.lng_rpgid=mmorpg.lng_id) ON (credits.lng_rpgid=mmorpg.lng_id) AND (credits.lng_serverid=servers.lng_id)
WHERE credits.lng_userid=23 // this will be replaced later in asp with a session variable
ORDER BY mmorpg.str_name, servers.str_name

now the problem is that i need to return the field servers as one field, depicted as an array delimited by ", ".

i need to know asap is this possible and if so how do i go about joining it sothat i get only one record per MMORPG record, but with all the servers listed in that field?

if not, i will just have to come up with another plan...

Pierre


All times are GMT -4. The time now is 05:40 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.