Get specific "version" of a column

Question

I have an app that uses SQLite to store data locally. There may be more than one file storing the data. All the files contain a list of items, but only one of them has the "correct" status for the current user (basically, there is a "user" db in $HOME and a "system-wide" one in /etc).

Usually, both files will contain the same list of items, and only the status column will differ. If, however, either contains items not in both, I want that data as well.

SQLite does not have FULL OUTER JOIN.

A solution I have come up with is this:

SELECT item, group_concat(status) AS status FROM (SELECT item,status FROM items UNION SELECT item,status FROM otherdb.items) GROUP BY item;

And then parsing the comma-separated "status" output to get the "right" status. I would like a pure SQL solution, however.

The values I want for status are:

If any = 1, status = 1
elif any = -1, status = -1
elif any = 2, status = 2
elif any = -2, status = -2
else status = 0 or NULL

status may only (in the db) be -2,-1,0,NULL,1,2 so this covers all data.

If there is a solution that only gives whichever one is non-zero and non-null, that could work too, although I would prefer the above.

Answer 1

I would sugest you one approach:

1. create a temp table, one adittional column for a flag "otherbd";
2. throw everything from the 2 tables in there;
3. delete the lines you don't want;

Answer 2

Create a status priority table with the following values

status  priority
  1       5
 -1       4
  2       3
 -2       2
  0       1
 NULL     0

The concept is to join your two tables against this StatusPriorty table, Group the records and use MAX(Priority) to get your results