Combining multiple Sqlite queries

Question

I'm struggling to combine a few sqlite queries in my android project.

I'm getting all the elements of my ItemTable with this call

String sql = "SELECT n.*, p.ParentID "
           + "FROM NameTable n, "
           + "     ItemTable p "
           + "WHERE p.itemID = n.OwnerID "
           + "COLLATE NOCASE";

Then get each of their parents with one of these two queries:

if (aItem.parentID > 0) {
    sql = "SELECT n.*, p.ParentID, f.FamilyID "
        + "FROM NameTable n, "
        + "     FamilyTable f, "
        + "     ItemTable p "
        + "WHERE f.FamilyID = '" + aItem.parentID + "' "
        + "  AND p.ItemID = n.OwnerID "
        + "  AND (p.ItemID = f.MotherID OR p.itemID = f.FatherID) "
        + "  AND n.IsPrimary = 1 ";
} else {
    sql = "SELECT n.*, p.ItemID, f.FamilyID "
        + "FROM NameTable n, "
        + "     FamilyTable f, "
        + "     ChildTable c, "
        + "     ItemTable p "
        + "WHERE c.ChildID = '" + aItem.ItemID + "' "
        + "  AND p.ItemID = n.OwnerID "
        + "  AND c.FamilyID = f.FamilyID "
        + "  AND (p.ItemID = f.MotherID OR p.ItemID = f.FatherID) "
        + "  AND n.IsPrimary = 1 ";
}

Is there any way I can combine these three queries and the logic into one? I would imagine that combining the calls would be faster, but I'm not sure how to combine them, and also not sure how to read the rows if I did combine them. I could really use some advice and assistance.

There are many ways, but if you keep the database schema and the purpose of the queries a secret, nobody can help you. — CL.
– CL., Commented Mar 17, 2013 at 10:15

Jeff Campbell · Accepted Answer · 2013-03-18 19:18:49Z

3

If you don't mind having the results of both queries in one result set, You can UNION the 2 queries together by by adding "UNION" between the queries, along with adding "AS" to the column names, and adding a new column to differentiate the results from each query

sql = "SELECT n.*, p.ParentID AS ID1, f.FamilyID, 1 AS RESULT_GROUP "
    + "FROM NameTable n, "
    + "     FamilyTable f, "
    + "     ItemTable p "
    + "WHERE f.FamilyID = '" + aItem.parentID + "' "
    + "  AND p.ItemID = n.OwnerID "
    + "  AND (p.ItemID = f.MotherID OR p.itemID = f.FatherID) "
    + "  AND n.IsPrimary = 1 ";
sql += " UNION "
sql += "SELECT n.*, p.ItemID AS ID1, f.FamilyID AS ID2, 2 AS RESULT_GROUP "
    + "FROM NameTable n, "
    + "     FamilyTable f, "
    + "     ChildTable c, "
    + "     ItemTable p "
    + "WHERE c.ChildID = '" + aItem.ItemID + "' "
    + "  AND p.ItemID = n.OwnerID "
    + "  AND c.FamilyID = f.FamilyID "
    + "  AND (p.ItemID = f.MotherID OR p.ItemID = f.FatherID) "
    + "  AND n.IsPrimary = 1 ";

Also on your joins, I would do something like (improve performance):

sql = "SELECT n.*, p.ParentID AS ID1, f.FamilyID, 1 AS RESULT_GROUP "
    + "FROM NameTable n "
    + "JOIN FamilyTable f ON f.FamilyID = '" + aItem.parentID + "' "
    + "JOIN ItemTable p ON p.ItemID = n.OwnerID"
    + "WHERE 
    + "  (p.ItemID = f.MotherID OR p.itemID = f.FatherID) "
    + "  AND n.IsPrimary = 1 ";

answered Mar 18, 2013 at 19:18

Jeff Campbell

1,6352 gold badges17 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HalR Over a year ago

Thanks Jeff, That's really what I was looking for. Sorry for the poorly worded question.

Collectives™ on Stack Overflow

Combining multiple Sqlite queries

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related