CODEWITHSUNDEEP
databasesqlite
Samuel
Samuel

Reputation: 17171

SQLite column names without table name

When querying a database in SQLite I find it useful to give alias to the tables I am working with. It also helps resolve naming conflicts.

SELECT a._id, a.name, b.email FROM people AS a, emails AS b

However then when I look for the columns "_id", "name", and "email" in my cursor, I get an error that the columns cannot be found. It only works if I change the query to:

SELECT a._id AS "_id", a.name AS "name", b.email AS "email" FROM people AS a, emails AS b

Is there a way to get the base column name without the extra work?

Upvotes: 3

Views: 1299

Answers (2)

Samuel
Samuel

Reputation: 17171

From what I've read, this appears to be a SQLite specific issue.

SQLite does not (currently) make any promises about column names on
queries that omit the AS clause. If you use an AS clause, then the
column name is guaranteed to be the label to the right of AS. If you
omit the AS clause, then the column names might change from one release of SQLite to the next.

-http://old.nabble.com/Aliasing-and-columns-names-td18382558.html

I would recommend only using the table name alias on columns that have conflict. For instance, in the example above, if _id is the only column with conflict do:

SELECT a._id AS _id, name, email FROM people AS a, emails

Upvotes: 0

reggie
reggie

Reputation: 13721

Declaring an alias to a column is a better way to avoid errors. The two tables the your querying may have the same column names and it can be difficult to differentiate them without aliases. The only way to differentiate them is by associating them with their table names or giving them aliases.

Aliases can make queries easier to both write and to read. You can give a table or a column another name by using an alias. This can be a good thing to do if you have very long or complex table names or column names

Upvotes: 2

Related Questions