Some help with SQLite Query

[Iczer]

I have SQLite database with one main table "mainTable" and couple daughter tables "daughterTable*" where "*" is from 1 and up (not known before hand)

what i need is to make sqlite query for search entries in main table, which is not exist in any (all) daughter tables.

maybe someone can help

$s_query_string = "SELECT ROWID,* FROM mainTable WHERE " & $columnName & " {is not exist in same column of daughterTable* }"

_SQLite_Query($db, $s_query_string, $hQuery)
While _SQLite_FetchData($hQuery, $aRow, False, False) = $SQLITE_OK
    ConsoleWrite("string --> " & $aRow[1] & " / " & $aRow[3] & @CRLF
WEnd

[mikell]

SELECT * FROM mainTable WHERE columnName NOT IN (SELECT columnName1 FROM daughterTable1) AND columnName NOT IN (SELECT columnName2 FROM daughterTable2)

?

[jchd]

Iczer,

This question clearly denotes a wrong database design. You should never have to treat schema names (tables, columns) as parameters.

I suspect that all your daughter tables share the same layout, while "something" makes data go in a particular table. The correct design is to add this "something" indicator to the existing columns and group all these table in that one. This is a step towards normalization of your database.

To make it easier to understand, you don't have separate phonebooks for, say, men and women. You group all entries in one phonebook and eventually add a gender field, should you need it.

[jdelaney]

Without the restructure above, which would be best, you'll need two queries, one to grab all tables like 'daughterTable%', and then loop through that return, and use it to drive another query to each table.  You'll have to google for the syntax of querying tables via mysql.

Interactive sql is like: select * from sysobjects

maybe start with something like: SELECT * FROM INFORMATION_SCHEMA.TABLES

Edited April 11, 2014 by jdelaney

[jchd]

That won't work with SQLite.

Use this:

#include <Array.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>

_SQLite_Startup()
Local $hDB = _SQLite_Open(@ScriptDir & "\myDB.sqlite")

Local $aRows, $iRows, $iCols
_SQLite_GetTable2d($hDB, "select name from sqlite_master where type = 'table' and name like 'daughter%';", $aRows, $iRows, $iCols)
_ArrayDisplay($aRows)

_SQLite_Close($hDB)
_SQLite_Shutdown()

Use actual DB filename and table prefix in the where clause.

Edited April 11, 2014 by jchd