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 |
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 2002-08-15 : 09:21:08
|
| Hi. I'm curious if you all have any tips on how I can implement ASP web pages that allow my users to build their own queries that run across multiple tables.I have done this successfully, albeit in a very unsmooth manner, with queries against a SINGLE table but I am having trouble envisioning how I might do this against multiple tables. My big mental block comes when I try to envision how I would translate the captured HTML form data and then translate that into SQL queries that run against the proper tables since multiple joins would likely be involved.Is English Query an option here?I'm running SQL Server 7.0 and my users run IE 5.0 and up on the desktop.Any help is appreciated.Thanks. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-15 : 09:34:03
|
It seems like English Query could work, but for it to be effective you need to have your database well-normalized and your tables defined with foreign keys. BUT...you also need to create semantics, synonyms, and so on too, and it looks like A LOT of work. And unless you enforce some kind of limits, people will still be able to put 2 or more completely unrelated tables in the query, and you either end up with cross joins or have to pre-process the statement to add the proper joins (I think English Query will do this, but I'm not sure)I'm naturally hesitant to let end users build advanced queries, and here's a good test: if they can't handle query analyzer, it's fair to say they lack the skills and knowledge to build their own queries themselves. Putting a slick web interface on it doesn't solve the problem, it only makes it easier for them to get into trouble and potentially FUBAR the database or server.Since you're banging your head on this, it might be a sign that maybe you should keep the web queries simple, and just add some generic, canned stuff for them to use, and allow the power users to use query analyzer, or linked tables in MS Access. It'll be a lot easier for you to manage. I can't see how the ASP code WON'T be a disaster, especially in trying to keep people from writing a bad query. I can say personally that whenever I ignored my better judgement took leave of my senses tried to include that kind of functionality I gave up pretty quickly when faced with the same prospect you're facing now. I just didn't like how the straightjacket fit.  Edited by - robvolk on 08/15/2002 09:43:18 |
 |
|
|
r937
Posting Yak Master
112 Posts |
Posted - 2002-08-15 : 11:23:41
|
| if the number of tables is not large, predefine some views that have the joins built in, and let the users choose which "table" they want data from, e.g.A = M - member dataB = MjoinH - member plus historyC = MjoinP - member plus preferencesetc.once they've made that choice, the next web page shows them which fields are available (from both tables in the join, but they don't have to know that)...you'll need to introduce them to the concept of one-to-many, so that when they choose Name and Preference in the "C" table, Name will repeat for every Preference...rudyhttp://rudy.ca/ |
|
|
|
|
|
|
|
|
dynamic web-based queries across multiple tables?