How to build FROM clause dynamically

I look trough the forum, but did not find any simular problem. Somebody, help, please!
What I need to do is to write an algorithm which create a FROM clause for SQL query, using tables and joined fields, specified by the user. There could be up to 25 tables with any type of join (INNER, OUTER, FULL, CROSS). I know the basic structure of the FROM clause: "from T1 inner(or other type) join T2 on T1.field=T2.field" etc., but the main problem that users can specify tables in any order and I have to re-arrange them to create valid statement.A SQL Server stored procedure is a poor option for giving users ad-hoc query capability. You would need to write a routine that parsed their input statement (very difficult considering that users have little understanding of relational databases) and then would create a logic execution plan from the statement given known relationships among tables.

Wait a minute...that's what Query Analyzer does! Why not just allow the user to submit adhoc query statements? (Make sure your security is tight and you have a query governor active!)

Either that, or check into some of the data-mining software packages such as DI Diver or Cognos.

Where do you live in Melbourne? I spent two years as a kid in Box Hill.|||Thaks for your reply,

but I am not writing the store procedure and I do not have an option using any packages, like Cognos, Crystal etc. I am writing the procedure in VB (it is not up to me). And the main idea, that uses SHOULD NOT HAVE any idea about relational database. Uses just say: I want SQL Server database(could be others - like Oracle, Sybase or MS Access), I supply them names of available servers, they choose the server, I supply names of availabe databases, then tables, then fields, they choose whatever the want, then they point at the related fields of the tables they chose before, and "magic" happened - they have a report. I've done almost everything, just bits and pieces left. AND a FROM clause! It works for simple queries, but for more complicated it works sometimes, which obviousy is not good enough. So I nee an algorithm and stuck with that.

I live at Moorabbin and I know Box Hill - very nice area. Where do you live now?|||*ack* the whole joining thing is the problem...

I have tried this before... basically unless you can query the db to find out what the foreign keys are you are kinda stuffed...

Otherwise the users have to know enough about the database to be able to define the relationships themseleves...|||it sounds to me that the best solution for your problem
which is:
dynamic sql statements
ad hoc queries
no sql knowledge at the end user

you are an excellent candidate for ENGLISH QUERY
There are sample apps available for this product
code samples and etc.

English Query (http://www.microsoft.com/sql/evaluation/features/english.asp)|||I think Ruprect's English Query suggestion is your best shot. The problem is that a user who does not know anything about relational database is more likely than not to get the WRONG ANSWER to a problem due to not understanding relational set manipulation. This is what DBAs and SQL developers are for.

Think about it. Basic SQL is not that complicated. If a user can't understand "Select columna, columnb from sometable where columnc = somevalue" they shouldn't be mucking about in a database anyway. I mean, the syntax is practically an English sentence anyway. Hey, how about a procedure that lets them submit it in Australian?

"Grab beer, prawns, lamington from cooler where label = 'Fosters'"

By the way, now I am back in the United States (Midwest), but I still remember my old address down under 25 years ago: 17 Simmons Street, Box Hill.|||if you build an application that can correctly join any combination of tables from 25 possible tables, whether SQL Server, Oracle, Sybase or MS Access, using the appropriate (often proprietary) sql, with joins utilizing the right columns as determined by an analysis of foreign keys in the information schema, then you have something which you can go out and sell as commercial software against cognos, crystal, etc.

in other words, it ain't as easy as you think|||Depending on how many tables the users are going to want to access and how fluid the ad-hoc queries are, you could work this in two other ways - which aren't elegant but might be enough to serve your purpose:

Either, restrict the queries that the users can create by offering them a list of possible query options that you have already generated the SQL for, or create a table that holds the correct joining criteria for your tables so that you can select the required code.

These won't work if you do want it to be a completely open ended query tool - but I would have thought that if you're getting to the point of ad-hoc queries using OUTER JOINS, then your users will probably have the SQL knowledge already...

Just a thought...|||If you just want to give them slice, dice, and filter capability on defined recordsets. then consider a pivot table linked to a view from either a spreadsheet or a web page.|||Originally posted by r937
if you build an application that can correctly join any combination of tables from 25 possible tables, whether SQL Server, Oracle, Sybase or MS Access, using the appropriate (often proprietary) sql, with joins utilizing the right columns as determined by an analysis of foreign keys in the information schema, then you have something which you can go out and sell as commercial software against cognos, crystal, etc.

in other words, it ain't as easy as you think

That is exactly what our company is trying to do and I am aware that is not easy task, but I have to do it somehow.|||in that case i would suggest investigating INFORMATION_SCHEMA views to see if you can create queries that can access the tables, column, and especially primary/foreign keys

i think with MS access you are up the creek, but i believe the other databases all support INFORMATION_SCHEMA

good luck and let us know how your project turns out