SQL select specific fields in JOIN query

i'm writing an Android app and i've run into a bit of a roadblock involving databases. the way Android handles databases, i cannot refer to names in the result set by the usual 'tablename.colname' method, so this presents a huge issue when any tables in the database contain the same column name. what further complicates the issue, is that any table that is used by a ViewAdapter to display the data to the user (as in my application), must contain a field named "_id" as an autoincrement primary key int. therefore, some tables MUST have identical column names. however, to avoid this, it is possible to use an "AS" clause in a statement to rename the value in question. however, i'm using a rather long statement and i don't know how to limit the columns returned on a JOINed table. what i have is this, and it's completely illegal in android due to the 'tablename.colname' references. i actually added the table names in to make the statement more readable, but i can't use them:

SELECT call._id AS android_call_id,
call.phone,
call.time,
call.duration
call.duration_billed
call.pending
call.call_id
call.job_id
FROM call

LEFT OUTER JOIN phone ON call.phone_number=phone.phone

LEFT OUTER JOIN job ON job._id=call.job_id

WHERE call.pending=1 ORDER BY job._id

but what i need, is to rename the job._id to something else using an "AS" statement, same as with the 'call._id' field in the first part of the query. how do i achieve this renaming in a JOIN?

edit:

progress so far. i think i've worked out the syntax errors, but i get another runtime error "no such column 'job._id', which may be related to @Tom H. comment

edit 2:

turns out Tom was right, and i adjusted accordingly, but it doesn't work:

SELECT call._id AS android_call_id,
call.phone,
call.time,
call.duration,
call.duration_billed,
call.pending,
call.call_id,
call.job_id,
job._id AS android_job_id,
job.job_name,
job.job_number
FROM call

LEFT OUTER JOIN phone ON call.phone_number=phone.phon开发者_C百科e

LEFT OUTER JOIN job ON job._id=call.job_id

WHERE call.pending=1 ORDER BY job._id

error:

05-24 16:50:37.561: ERROR/Minutemaid - Service(7705): oops: ambiguous column name: call._id: , while compiling: SELECT call._id AS android_call_id,call.phone_number,call.time,call.duration,call.duration_billed,call.pending,call.call_id,call.job_id,job._id AS android_job_id,job.job_name,job.job_number FROM call LEFT OUTER JOIN phone ON call.phone_number=phone.phone LEFT OUTER JOIN call ON call.job_id=job._id WHERE call.pending=1 ORDER BY job._id

Can't you simply use AS to alias all of the tablename.columnname references to unique names in the result set?

You can simply create a VIEW that restricts columns selectable in a table and assigns another name to them.

You can try massaging the table names before you join them by using sub-queries with AS in the FROM clause. For example:

select c_phone, c_id, p_id
from (select id as c_id, phone as c_phone, phone_number as c_phone_number, ... from call) as c
left outer join (select id as p_id, phone as p_phone, ... ) as p
  on c_phone_number = p_phone
...

If the limitation is just that you can't use table names to distinguish between columns but can use correlation names then simpler is:

select c.id, c.phone, p.id as "p_id" from ... call c join phone p