I am writing an Android app involving cars and parts.
The thing I am seeking to do is offer a ListView of all the parts in the db excluding the parts already assigned for a specific car.
I am able to get the results I want with the following query with an EXCEPT statement in it.
SELECT DISTINCT Parts._id, Parts.PartNumber, Parts.PartName, Parts.MFG, Parts.Barcode, Parts.Notes, Parts.Photo
FROM Parts
JOIN CarsToParts
WHERE CarsToParts.CarID != carID
EXCEPT
SELECT DISTINCT Parts._id, Parts.PartNumber, Parts.PartName, Parts.MFG, Parts.Barcode, Parts.Notes, Parts.Photo
FROM Parts
JOIN CarsToParts
WHERE CarsToParts.CarID = carID AND CarsToParts.PartID = Parts._id
The problem I'm having is I'm getting a Cursor result that includes the table name as part of the key names as follows:
I/System.out: >>>>> Dumping cursor
android.database.sqlite.SQLiteCursor@39719c0
I/System.out: 0 {
I/System.out: Parts._id=21
I/System.out: Parts.Photo=<unprintable>
I/System.out: Parts.PartName=Oil Filter
I/System.out: Parts.PartNumber=116 354 733
I/System.out: Parts.MFG=Mercedes Benz
I/System.out: Parts.Barcode=254485880
I/System.out: Parts.Notes=
I/System.out: }
I/System.out: <<<<<
When I try to send this to the CursorAdapter it chokes on it.
The CarsToParts table has the following columns:
_id
CarID
PartID
The CarsToParts table allows me to map the same part to two different cars, which is often a possibility with cars from the same manufacture.
Is there a way to get SQLite to not include the table name as part of the key namesin the Cursor output?
EDIT: Upon further investigation I find CursorAdapter requires the _id column to be present. Which it is if only I can get SQLite to not include the table name as part of the key names.
The column names returned by a query are unspecified, unless you use AS:
SELECT DISTINCT Parts._id AS _id, Parts.PartNumber AS PartNumber, ...