On 2 Jan 2019, at 9:50pm, Jonathan Moules <jonathan-li...@lightpear.com> wrote:
> Sorry, but which column is ambiguous? The users.item_id is a foreign key to > the item_info.item_id - that's why it's a "REFERENCES" - why would I want to > change it to be something else? Isn't the convention for FK's to have the > same name across tables? That's what "USING" is for right? (or NATURAL, but I > prefer to be explicit.) Happy to be corrected. It may be that our careers developed with different ideas about how to use SQL. You had a JOIN, both tables had a column "item_id", and a reference inside the JOIN to "item_id" would be ambiguous. Since the SQL standard does not make it clear which table would be used, it would be possible for different implementations of SQL to think you meant different tables. I understand that, in your example, the values would be the same. But that doesn't explain to you what the optimizer thinks you're trying to do. The simplest way to tell the optimizer what you need would be to rename one of the columns. You could try both tables, see which solution was faster, and use that one. However, I see other posters have gained better clarity for your problem. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
