Catch-all Join statement

[ober0330]

I'm just wondering if this is even possible: I'm building a query program for our users. There are 5 tables that they could gather various pieces of data from, and I'm wondering if there is a "FROM" statement that I can just tack on that will gather whatever data they input, so I don't have to worry about building custom FROM statements. I've tried a variety of combinations with INNER JOINS, but if I include more than 2 tables, it doesn't return anything.

Any ideas?

edit: Using SQL Server 2K, by the way.

[vb_dba]

Have you looked at using a Left or Right join instead of an Inner Join? If you ever get your tables joined and return records, I'd stick that in a View, then have your users run their queries against that view, that way you don't have to dynamically create the "From" statement each time they run a query.

[ober0330]

But aren't views looking at the same records all the time? I want them to be able to dynamically pick the fields that are going to be in the report.

[brucevde]

Views and/or SQL statements are the only two options to use when it comes to creating ad-hoc queries. To account for all possible scenarios you may need to create multiple Views. The user chooses the View they want and then they pick the fields they want from the view.

Dynamically building the sql statement shouldn't be that difficult but it would depend on how the 5 tables are related.

[vb_dba]

You would expose all fields from all 5 tables through the view, so that the users could select which fields they wanted to include on the report. Say I wanted to create a view on 2 tables:

Create View vw_Report
As

Select t1.recID
     , t1.fname
     , t1.lname
     , t2.dept_id
     , t2.dept_name
From table1 t1 Inner Join table2 t2 On t1.dept_id = t2.dept_id

Then in your calling application, the users could select any of the fields listed in the Select clause of the view and you could build your report statement like:

Dim sSql As String
 
sSql = "Select lname, dept_name From vw_Report Where lname ='blah'"

For your report, you would need to list all fields from all five tables so that they are exposed via the view for your end users. The View is only there to eliminate the need to perform all of the table joins in your client application.

[szlamany]

If you are using SQL 2K, then you need to evaluate why you aren't using STORED PROCEDURES with PARAMETERIZE arguments...

You can have IF statements, and multiple SELECT statements - it's why you use SQL 2K anyway...