Selecting from multiple tables in VBScript/ASP

Question

I understand that this question is based on a very old programming language, and is also based on some poor practices with database design, but I am hoping for some guidance as my VBScript is not up to scratch, and at my job I am unable to change my database structure or my scripting language.

I am working on a webpage that gets its data from a database which originally contained all of its data in one table. However, the database managers at my work have decided to split the large table into many smaller ones. Our webpage contains an online map with point data, which displays in a new page a table of data for each point found. The code to connect to the database originally looked something like this:

sql_select = "SELECT * FROM table_name WHERE master_id='"&key&"'"
Set rs = Conn.Execute(sql_select)

This code worked fine for what it was used for. However, now that the main table has been divided into seven sub-tables, I am in need of a code that will select all data from all tables, and then filter them based on their master id.

I feel like I understand the theory of how to make this work (I am not a computer science major, so this is still somewhat foreign to me), and that I need to implement a union. My main problem is dealing with the syntax of VBScript/ASP in creating this script, as it seems like everything I try doesn't work.

Can anybody please lend me some guidance? Much appreciated!

Answer 1

It sounds like what you actually need is a JOIN rather than a UNION. A JOIN glues together data from several places into a single row, while a UNION glues together several rows of data in a collection of more rows:

The DB Query

In this case you have a master table and several sub tables. For the example's sake I'll assume the tables look like:

Table         Columns
---------------------------
MasterTable   MasterId, M1, M2, M3
SubTableA     SubTableAId, MasterId, A1, A2, A3
SubTableB     SubTableBId, MasterId, B1, B2, B3

So if you were wanting to retrieve the columns M1-3 and A1-A3 then you could join the columns from MasterTable with the columns on SubTableA where they have matching MasterId values:

SELECT *
FROM MasterTable MT 
INNER JOIN SubTableA STA ON MT.MasterId = STA.MasterId

The MT and the STA are aliases so we can don't have to type out the entire tables names when we're clarifying which of the two master id's we're referring to.

If we then only wanted the values for a single, specific master ID we could add that as a where clause on the end:

SELECT *
FROM MasterTable MT 
INNER JOIN SubTableA STA ON MT.MasterId = STA.MasterId
WHERE MT.MasterId = ?

If we needed columns from additional tables, we could JOIN to those tables as well:

SELECT *
FROM MasterTable MT 
INNER JOIN SubTableA STA ON MT.MasterId = STA.MasterId
INNER JOIN SubTableB STB ON MT.MasterId = STB.MasterId
WHERE MT.MasterId = ?

We are specifying an INNER JOIN because we only want records returned where there are matching values in all three tables. If SubTableB only had values for each master id some of the time, then we would want to switch to a LEFT JOIN or LEFT OUTER JOIN which would tell the database to return our columns from the LEFT side of the join even when there aren't matching columns available on the right side.

So to get all columns in a situation where we may not always have SubTableB records but still want the Master and SubTableA columns:

SELECT *
FROM MasterTable MT 
INNER JOIN SubTableA STA ON MT.MasterId = STA.MasterId
LEFT JOIN SubTableB STB ON MT.MasterId = STB.MasterId
WHERE MT.MasterId = ?

if it is possible that you will have multiple records in one of the SubTables for a single MasterId then the rows will be returned with each possible combinations of the table rows that match the JOIN criteria.

So if we had one record in the MasterTable with an ID of 5, 1 in SubTableA with a master id of 5, and 3 in SubTableB with an id of 5, then we would actually receive 3 rows back, 1 for each combination of MasterTable and SubTableA values with the SubTable3 values. So in your client-side you will either need to be able to handle the duplicate values/rows or you split the query to execute the query from SubTableB separately.

VBScript SQL Code

The code you provided, while a sample, has two downsides. One, because you are concatenating the argument into the database string, special characters in that string could break your SQL statement or, if you are retrieving that value from a form POST, Querystring, etc, then an end user could actually inject a SQL statement into the variable to run on your database (potentially dropping valuable records, manipulating the database, or potentially even uploading trojans or executables to the server, depending on the permissions level). The second downside is that there are potential downsides to executing a string like that. Depending on your database engine you could take minor performance hits from implicit type conversions, lack of plan caching, etc simply because the statement was delivered as a non-parametrized string.

To resolve these issues, you should look into using parametrized SQL statements or creating a stored procedure for your statement and calling that with parametrized values. the ADODB object has support for parameters:

Dim yourKeyValue : yourKeyValue = 5

Dim objConn, objCommand, rsResults
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open("Your connection string")

Set objCommand = Server.CreateObject("ADODB.Command")

objCommand.ActiveConnection = objConn
objCommand.CommandText = "YourStoreProcName"
objCommand.CommandType = 4   'stored proc type/enum

objCommand.Parameters.Append objCommand.CreateParameter("Key", 3,1,,yourKeyValue)          
set rsResults = objCommand.Execute()

More information on parameters and constants: http://www.w3schools.com/ado/met_comm_createparameter.asp

More info on CommandType: http://www.w3schools.com/ado/prop_comm_commandtype.asp

You could change out the command text for a SQL string instead of a stored procedure and use ?-marks as placeholders for the parameters you define.

More Cleanup

Just a little more cleanup real quick.

Rather than specifying a * for your query, you will probably want to specify the list of columns you actually want returned. This will reduce the amount of data coming across the wire to only what you need, tell the database exactly what you need so it doesn't have to pre-lookup the fieldnames on it's own, and it will reduce some of the confusion in your recordset object (you would otherwise have quite a few master id columns, for instance).

As Dee mentioned in their response, you probably should include whoever made the database changes, but I don't think you want to just dump this in their laps because you will want to understand why and how it works, otherwise trying to make additional changes or maintaining the app will be that much harder than it already is.

Answer 2

The database managers split the table up, tell them you need a new SQL statement to replace the one you have. Since they know the new structure, they can write it for you in a couple of minutes.