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.
I fixed the typo (a stray comma!), and of course, the REFERENCES table
has to be second, not first. Sorry, should have checked
CREATE TABLE item_info (
item_id TEXT PRIMARY KEY ON CONFLICT IGNORE
NOT NULL
COLLATE NOCASE,
more_data TEXT
);
CREATE TABLE users (
item_id TEXT REFERENCES item_info (item_id)
NOT NULL
COLLATE NOCASE,
some_data TEXT
);
CREATE INDEX users__item_id__idx ON users (
item_id
);
-------
In the end to get the desired result I had to invert the query by
keeping the WHERE clause inside - this one gets the full speed without
needing the WHERE clause twice:
SELECT
*
FROM
item_info
JOIN (select count(1) from users where item_id = ?)
USING (item_id);
Anyway, just an observation.
Thanks,
Jonathan
On 2019-01-02 20:33, Simon Slavin wrote:
On 2 Jan 2019, at 4:44pm, Jonathan Moules <jonathan-li...@lightpear.com> wrote:
SELECT
*
FROM
item_info
JOIN (select count(1) from users group by item_id)
USING (item_id)
where item_id = ?;
You have an ambiguous column name, and I don't think SQLite is doing what you
think it's doing.
Please change the name of the column users.item_id to something else, then try
your SELECT again.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
