5 replies to this topic

[felix]

Having problems in a vbscript with creating a query to retreive information from an msi.

Here's the base query
SELECT `File`.`FileName`, `File`.`Component_`, `File`.`Version`, `ModuleFiles`.`ModuleName` FROM `File`, `ModuleFiles` WHERE `File`.`FileName` = `ModuleFiles`.`FileName`

this works fine, ModuleFiles is a custom table, that basically contains data about all existing mergemodules that are available to use in the project, such as module names, filenames, filesize, fileversions. the query currently returns the filename, component, fileversion, and corresponding module name for all files in the file table that exist in the mergemodules that are being used for the project.

Thats all fine, but I wanted to refine the query so that at the same time it would eliminate any records from the list where the corresponding component to the file is found in the Component field of the ModuleComponents. Unfortunately it won't accept the query, and the vbscript crashes.

SELECT `File`.`FileName`, `File`.`Component_`, `File`.`Version`, `ModuleFiles`.`ModuleName` FROM `File`, `ModuleFiles`, `ModuleComponents` WHERE `File`.`FileName` = `ModuleFiles`.`FileName` AND `File`.`Component_` <> `ModuleComponents`.`Component`

All the checks to make sure that the relevant tables exist in the msi have already been completed.

Now what really bugs me is the if you change the last part of the condition to equate to instead of not equating it accepts the query fine. So this next identical query, except for the <> changing to a =, works perfectly fine.

SELECT `File`.`FileName`, `File`.`Component_`, `File`.`Version`, `ModuleFiles`.`ModuleName` FROM `File`, `ModuleFiles`, `ModuleComponents` WHERE `File`.`FileName` = `ModuleFiles`.`FileName` AND `File`.`Component_` = `ModuleComponents`.`Component`

Can anyone explain why, and in turn provide me with a solution or even point to where I can figure out the solution to be able to do what I want to do. Both fields in question are strings which means that according to the msdn I can only use = or <>, but in this case its refusing to allow the <>. Its not a circular query either with is not supported in the SQL engine used in windows installer.

It will be quiet annoying if I can't get the required results with a single query since any additional processing would be relatively inefficient particularly when working on the larger applications.

Thanks in advance for any help

[Stefan Krueger]

Did you try with parentheses:
SELECT `File`.`FileName`, `File`.`Component_`, `File`.`Version`, `ModuleFiles`.`ModuleName` FROM `File`, `ModuleFiles`, `ModuleComponents` WHERE (`File`.`FileName` = `ModuleFiles`.`FileName`) AND (`File`.`Component_` <> `ModuleComponents`.`Component`)

[felix]

tried that, didn't appear to make any difference. What's really bugging me is why the query with just the = swaped for <> works, but the one using <> doesn't.

Edited by felix, 10 January 2004 - 20:41.

[LaurenceStarks]

According to the Platform SDK (ms-help://MS.PSDK.1033/msi/setup/sql_syntax.htm)

A WHERE {operation-list} clause is optional and is a grouping of operations to be used to filter the selection. The operations must be of the following types:


{column} = {column}
{column} = | <> | > | < | >= | <= {constant}
{column} = | <> | > | < | >= | <= {marker}
{column} is null
{column} is not null


Column to column not equals comparisons are not supported.

[felix]

Doh! It would appear that despite reading the documentation I am totally blind.

Thanks for pointing that out, although I really should have spotted it before now.

[Zardoz]

One point to remember is that there is no implied join between tables so you cannot ask for records that do not match a criteria until you have actually retrieved the record.