Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Marcos Leal
Starting Member
1 Post |
 Posted - 2001-01-07 : 11:40:59
|
| Hello! Everytime I look at my problem I think: "No... It's impossible to be so hard to solve. I must be doing something wrong...". But I can't seem to find the solution. My problem consists of a table and 2 or more many-to-many relationships with other tables. I'll give an example of what I mean. Let's think about a movie database. It has a movie table, an actor table and another table I'll call movietheater. The movie relate to the actor table in a many-to-many relationship. The movie also relates to movietheater in a many-to-many relationship. Now imagine I want to list all movies and in each one I'd like to show the movietheaters it's on display and all the actors of the movie. All right. Now let's put it in the real world. MS-SQL7 and ASP. The basic solution would be listing the movies and for each movie querying the DB for the related data... But it's impossible to do because I would use too much resources from the database to connect several times (two or more for each movie). Then my question was: "Can I get 1 recordset that solves my problem?". I simply can't think of one. What I would like to do is to get a recordset that agregates data in a certain way, like, 1 record with "Movie Name; Actor A, Actor B, Actor C; Movietheater1, Movietheater2"... I mean... Like a SUM(actor) that "sums" strings... :) But as this is impossible, I thought about using Cursors... Is this the best solution??? But wait! There's more! :) The basic recordset (let's say movies) is the result of a very complex search involving the DB and text search in files outside the DB (related in another table). To make this search I'm creating a query string into a variable and executing it. As I saw in this site, I can EXEC(@qstring) and this will result in the right recordset. But I need to use this recordset in the CURSOR.... You will think... All right... Just put the recordset into a temporary table and use the cursor over it. But them I have another problem. As I'm using ASP and internet users are anonymous, I use only 1 user to connect to the DB. So the same temporary table will be shared by all internet users and there's a big chance of one interfeering in the other's search. And now what ? This is the result of several hours thinking about this problem. I believe I might have gone by the wrong way. But if there's a detour I could have gotten in the begining of the road... Please enlighten me! Thank you all.Marcos Leonel Lealmarcos@leal.comBrazil |
|
|
|
|
|
A basic problem. Hard to solve.