SQLite – Get Rows with Common Entry in Second Column

androidsqlite

I am working on an Android studios project and I have a table that has two columns item and listname.

I want to get all the items that have a common Listname so in this case if I were looking for items that have Market as a listname I only want to get the last 4 from the table, I only want to return the items. I have this but it keeps crashing.

   public Cursor getItem(String listname) {
    SQLiteDatabase db = this.getWritableDatabase();

    String selectQuery = "SELECT  * FROM " + LIST_ITEM_TABLE + " WHERE "
            + ItemListName + " = " + listname;

    Cursor c = db.rawQuery(selectQuery, null);
    
    return c;
}

Best Answer

String have to be in single quotes

Also i don't see ItemListName be filled, i hoe there is Listname in it

See like:

String selectQuery = "SELECT  * FROM " + LIST_ITEM_TABLE + " WHERE "
        + ItemListName + " like  '" + listname +"'";

Related Solutions

Database design: nested tagging

I don't see much difference in the two designs. Whether you have 2 tables for the tags or 1 table with a type attribute (that can take 2 values), you basically going to need the same (in matters or complexity and structure) queries.

What is different is your decision to materialize the relationship between items and the section_tags, the tags that are "inherited" to them through the relationship with the sections. But that can be done with either the first or the second design. And yes, it would simplify querying (on the expense of making harder to have the materialized tables/views in sync.)

So, about the choice of design, if as you say the item and the section tags are two different types of tags, either design would be ok and I don't think it would make much difference, except on the details in the interaction between applications and the database.

For the materialization part of question, I think the best would be to test both options. If you are to make a decision fast, you could take the first path. Keep the design simple and only if you have efficiency issues that can't be overcome, examine the option of materialization as it would be quite complicated (as you have already thought) to keep it synced.

SQL Query – How to Query for Leaf Nodes in a Hierarchy Using a Closure Table

I think this is what you want (edited):

-- ml : short name for middle-to-leaf connection
-- rm : short name for root-to-middle connection

SELECT                                     -- show me 
    leaf.*                                 -- all
FROM 
    as_node AS leaf                        -- leaf nodes
  JOIN as_node_closure AS ml               -- which have an ancestor
    ON  ml.descendant = leaf.id            -- 
  JOIN as_node AS middle                   -- (let's call it middle)
    ON  ml.ancestor = middle.id            -- 
    AND middle.name = 'trumpet'            -- with the name 'trumpet'
  JOIN as_node_closure AS rm               -- and this middle
    ON  rm.descendant = ml.ancestor        -- has another ancestor
  JOIN as_node AS root                     -- (which we call root)
    ON  rm.ancestor = root.id              -- which is indeed a root node
    AND root.parent IS NULL                -- as it has no parent
WHERE                                      -- and the distance from root to middle
      rm.depth = 2                         -- is 2 (so middle's absolute level is 2)
  AND leaf.name LIKE '%.wav'
  AND NOT EXISTS                           -- and also the leaf is indeed a leaf node
      ( SELECT *                           -- as it has no children
        FROM as_node AS extra
        WHERE extra.parent = leaf.id
      ) ;

Test at SQL-Fiddle (where 'FT' is used instead of 'trumpet', to match your data.)

Best Answer

Related Solutions

Database design: nested tagging

SQL Query – How to Query for Leaf Nodes in a Hierarchy Using a Closure Table

Related Question