Simon,
thanks for replying.
Specify which names you want SQLite to use:
SELECT a, b, table2.c AS table2, alias.c AS alias
FROM table1
LEFT JOIN table2 ON ...
LEFT JOIN table2 AS alias ON ...
However, I strongly advise against having one string which is both a table name and a column
name ("table2" in your example). I cannot right now think of a problem this will
trigger but I suspect you're just setting yourself up for later confusion. Perhaps use
something like <table2_c>.
Well, I know I can specify every column in the query, but that's not the
question. Perhaps I didn't explain correctly. Sorry for my bad english.
The question is that there is a query where there are two JOINs over the
same table. Let's use a more real example: an internal message system.
This could be a sample query:
SELECT subject, sender.name, receipt.name
FROM messages
LEFT JOIN people AS sender ON messages.idsender=people.id
LEFT JOIN people AS receipt ON messages.idreceipt=people.id
That retrieves a list for messages, with columns: <subject>, <sender
name>, <receipt name>. But if I need the column's fullname, I get:
messages.subject, people.name, people.name
And yes, I know that I can specify an alias for those columns which
could be ambiguous. That is "sender.name AS sender_name" and
"receipt.name as receipt_name", but what about a query like "SELECT
messages.*, sender.*, receipt.* ..." which will be processed later by a
reporting system that knows nothing about original columns? In this
case, it's absolutely needed to distingish between <sender.name> and
<receipt.name>, <sender.departament> and <receipt.departament>, and so on.
So, the question is how to get the alias table name for every column in
the query. Of course, SQLite retrieves correctly all data for the query
(I mean that retrieved data has columns from sender and receipt), but
seems that it's not possible to get the alias table name. BTW, in my
work I use both SQLite and MySQL/MariaDB, and MySQL/MariaDB allows to
get that information (there are <table> and <org_table> fields in
MYSQL_FIELD struct). I only have problems with SQLite.
TIA,
Jose F. Gimenez
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
