vendredi 6 mai 2016

SQLite 3 CROSS or INTERSECT complex Subqueries

I have 3 related tables. One table has the rows that I am actually looking for, another table has the Data that I need to be Searching and the Third table describes What data I am looking for. I am getting undesired results from the following query :

SELECT * FROM names WHERE namesKey IN ( SELECT namesKey FROM data WHERE 
    ( dataType IS 3 AND data IS 'COINCIDENCE' ) 
    AND ( dataType IS 2 AND data IS 'STATE' ) 
    AND ( dataType IS 1 AND data IS 'COUNTRY' ) );

I need help making a query based on Multiple rows from the filter table. I need the rows which correspond to the keys from the second table that exist on multiple rows... I am explaining badly... here is an example :

DROP TABLE IF EXISTS names ;
CREATE TABLE names (
    namesKey INTEGER PRIMARY KEY ASC,
    name TEXT NOT NULL
);
DROP TABLE IF EXISTS data ;
CREATE TABLE data (
    dataKey INTEGER PRIMARY KEY ASC,
    namesKey INTEGER NOT NULL,
    dataType INTEGER NOT NULL,
    data TEXT NOT NULL,
    FOREIGN KEY(namesKey) REFERENCES names(namesKey)
);
DROP TABLE IF EXISTS filter ;
CREATE TABLE filter (
    filterKey INTEGER PRIMARY KEY ASC,
    dataType INTEGER NOT NULL,
    data TEXT NOT NULL
);

INSERT INTO names( name ) VALUES ( 'name1' );
INSERT INTO names( name ) VALUES ( 'name2' );
INSERT INTO names( name ) VALUES ( 'name3' );
INSERT INTO names( name ) VALUES ( 'name4' );
INSERT INTO names( name ) VALUES ( 'name5' );
INSERT INTO names( name ) VALUES ( 'name6' );
INSERT INTO names( name ) VALUES ( 'name7' );
INSERT INTO names( name ) VALUES ( 'name8' );
INSERT INTO names( name ) VALUES ( 'name9' );

INSERT INTO data( namesKey, dataType, data ) VALUES ( 1, 1, 'COUNTRY' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 1, 2, 'STATE' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 1, 3, 'CITY' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 2, 1, 'COUNTRY' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 2, 2, 'STATE' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 2, 3, 'OTHERCITY' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 3, 1, 'COUNTRY' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 3, 2, 'STATE' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 3, 3, 'COINCIDENCE' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 4, 1, 'COUNTRY' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 4, 2, 'OTHERSTATE' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 4, 3, 'COINCIDENCE' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 5, 1, 'OTHERCOUNTRY' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 5, 2, 'RANDOM' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 5, 3, 'COINCIDENCE' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 6, 1, 'OTHERCOUNTRY' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 6, 2, 'OTHERSTATE' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 6, 3, 'COINCIDENCE' );

INSERT INTO filter( dataType, data ) VALUES ( 1, 'COUNTRY' );
INSERT INTO filter( dataType, data ) VALUES ( 2, 'STATE' );
INSERT INTO filter( dataType, data ) VALUES ( 3, 'COINCIDENCE' );

Now what I need is to be able to run 3 different types of queries relatively reliably.

  1. I need to search for "No Data" and get names 7, 8, and 9

    • This one is Easy :

      SELECT * FROM names WHERE namesKey NOT IN ( SELECT namesKey FROM data ) ;
      
      
  2. I need to Search based on a Single type of data from the data table

    • Also Easy, Desired Result 3, 4, 5, and 6

      SELECT * FROM names WHERE 
          namesKey IN ( SELECT namesKey FROM data WHERE 
              ( dataType IS 3 AND data IS 'COINCIDENCE' ) ) 
      ;
      
      
  3. I need to Search based on Multiple rows From The filter table. This one I don't know how to do...

    • Desired Result is the name3 row ONLY
    • I Could do it by

      SELECT * FROM names WHERE 
          namesKey IN ( SELECT namesKey FROM data WHERE 
              ( dataType IS 3 AND data IS 'COINCIDENCE' ) )
          AND 
          namesKey IN ( SELECT namesKey FROM data WHERE 
              ( dataType IS 2 AND data IS 'STATE' ) )
          AND 
          namesKey IN ( SELECT namesKey FROM data WHERE 
              ( dataType IS 1 AND data IS 'COUNTRY' ) )
      ;
      
      
    • But that is just Ugly with a capital UGH!

And even worse with that approach, the dataType is theoretically arbitrarily large and thus I might end up trying to string together dozens or even Hundreds of sub queries... I could run out of RAM just composing my string before even Trying to put it into the SQL. So I am looking for a more elegant solution. Any suggestions?

Aucun commentaire:

Enregistrer un commentaire