Блог

Мои две таблицы — UserLog и UserInspection такие, что UserLog UNION ALL UserInspection .

Я хочу сохранить все записи из UserLog , но хочу сохранить некоторые записи из, UserInspection значение OWNER которых совпадает со column owner значением.

Иллюстрация:

 String OWNER = "0394850935803";

ArrayList<HashMap<String, String>> list = 
         new ArrayList<HashMap<String,String>>();

HashMap<String, String> dataMap = null;

table_log = "SELECT Type_Log, Start AS SortDate, Location, 
             Remark, Odometer, NULL AS Start_odo, NULL AS owner 
             FROM UserLog WHERE deleted !=1 ";

table_insp = "select Type, Date, Location, Observation, NULL, 
              Start_odo, owner from UserInspection where deleted !=1 ";

final_query = table_log   " UNION ALL "   table_insp   " ORDER BY SortDate";

cur = dbAdapter.rawQuery(final_query, null);

cur.moveToFirst();

for (int j = 0; j < cur.getCount(); j  )
{
    /*Keep all the UserLog records but keep only*/
    /*those UserInspection records whose owner column value*/
    /*from cursor matches the OWNER String value*/
    if((cur.isNull(cur.getColumnIndex("owner"))) 
     || OWNER.equals(cur.getString(cur.getColumnIndex("owner")))){

      /*Code to get other column values*/
      dataMap = new HashMap<String, String>();
      dataMap.put("Location", location);
      list.add(dataMap);
    }
}
  

Но я ничего не получаю в ArrayList list даже когда UserLog в таблице есть некоторые записи.

Просто поместите дополнительное условие в предложение WHERE, тогда вам не нужно проверять его в своем коде:

 ...
table_insp = "select Type, Date, Location, Observation, NULL, " 
             "Start_odo, owner from UserInspection " 
             "WHERE deleted !=1 " 
               "AND owner = ?";

final_query = table_log   " UNION ALL "   table_insp   " ORDER BY SortDate";
String[] parameters = new String[] { OWNER };

cur = dbAdapter.rawQuery(final_query, parameters);
...
  

Если вы можете иметь дело с содержимым второй таблицы в качестве дополнительных столбцов, тогда используйте left outer join :

 select *
from (SELECT Type_Log, Start AS SortDate, Location, 
             Remark, Odometer, NULL AS Start_odo, NULL AS owner 
      FROM UserLog
      WHERE deleted <> 1
     ) table_log left outer join
     (select Type, Date, Location, Observation, NULL, 
             Start_odo, owner
      from UserInspection
      where deleted <> 1 
     ) table_insp
     on table_log.owner = table_insp.owner;
  

Если вам действительно нужны отдельные строки, вы можете использовать with :

 with table_log as 
      SELECT Type_Log, Start AS SortDate, Location, 
             Remark, Odometer, NULL AS Start_odo, NULL AS owner 
      FROM UserLog
      WHERE deleted <> 1
    ),
     table_insp as (
      select Type, Date, Location, Observation, NULL, 
              Start_odo, owner
      from UserInspection ui
      where deleted <> 1 and
            exists (select 1 from table_log where ui.owner = table_log.owner)
     )
select *
from table_log
union all
select *
from table_insp;