Database Queries based on rules

[obrien.james]

Hi,

I have an idea about a project that I would like to start, but am unsure about the feasibility of it. I have a fairly large relational database (MySQL) with client information in it, spread over multiple tables and want to build small programme that will allow me to extract data from it without having to write complex SQL queries all the time.

Ideally I would want to be able to build a 'search' based on rules, where I pick whether to include or exclude the customer based on whether the rule passed or failed. Building the interface etc... Shouldn’t be too much of a problem, but I’m unsure how to go about converting my 'rules' into executable SQL which will either include or exclude the customer.

If anyone could help me I would be very grateful


James

[si_the_geek]

You would basically write the SQL statement in chunks, and only add in the bits that are relevant before running it.

Here is a simple VB6 example:

    Dim strSQL as String, strWhere as String
        'build the SQL statement based on what the user typed in txtSearch and txtAnotherSearch
      strSQL = "SELECT * FROM tbl_master"
                       '(find the conditions based on your controls)
      If txtSearch.Text <> "" Then strWhere = strWhere & " AND Field2 = " & Val(txtSearch.Text)
      If txtAnotherSearch.Text <> "" Then strWhere = strWhere & " AND Field7 = " & Val(txtAnotherSearch.Text)
                       '(put the conditions into the SQL statement, without the first And)
      If strWhere <> "" Then    
        strSQL = strSQL & " WHERE " & Mid(strWhere, 5)
      End If

This example is taken from the FAQ article ADO Beginners Tutorial -Some Further Steps, which includes a bit more information.

[obrien.james]

Thanks for the reply.

As the data about our customers is held over a number of tables how do you deal with the joins as well as multiple conditions for each rule?

I can imagine the logic getting very complex

Is it possible for each rule to run as a separate query and just join the results in SQL ??

James

[si_the_geek]

It depends on the circumstances, but it is likely to be valid to always put all of the joins in. If there are tables that aren't used, there is a good chance the joins will be ignored.

If not, adding joins as needed isn't a big deal - just use the same kind of method for the From clause that I showed above for the Where clause.