1) In the following LEFT JOIN, is it possible to alter
the query so that there is no w. prefix at the
beginning of each returned column name:
SELECT w.* FROM Words AS w LEFT JOIN Words as w2
ON w.Id=w2.Id AND w.Lang=w2.Lang AND w.Rev<w2.Rev
WHERE w2.Id IS NULL
Explanation of the query:
Each record corresponds to a word in a specific language:
CREATE TABLE Words (Lang VARCHAR(2), Id INTEGER, Rev INTEGER, Word
VARCHAR, PRIMARY KEY (Lang, Id, Rev))
Id's are word id's as opposed to record id's. Words that mean
the same thing across different languages have the same Id.
If a word is updated in a particular language, the Rev number
is increased (and the lower number entries under that
Id/Lang are obsolete). One way to view this is the phrases
that must be translated for a web site that must be
internationalized, but it is just as applicable to orders
from various customers (the Rev number would indicate
distinct orders).
The query above extracts the record with the highest number
Rev entry for each distinct Lang/Id pair. It does this by
crossing the two tables (LEFT JOIN) on the same Id and Lang
fields. When w.Rev is highest, there will be no corresponding
w2.Rev, so that field will be NULL, which is what the WHERE
selects for.
2) Is there a more optimal way to set this query up
(I'm calling to SQLite from PHP)? Possibly through
GROUP BY / HAVING?
For the last question, suppose that I've got a particular Id that
I'm interested in (Let's say 527) and I want all the highest Rev
entries for that Id.
3) Which of the following two queries is more efficient (Ie. is
it better to have the w.Id=527 before or after the WHERE keyword)?
SELECT w.* FROM Words AS w LEFT JOIN Words as w2
ON w.Id=w2.Id AND w.Lang=w2.Lang AND w.Rev<w2.Rev
WHERE w.Id=527 AND w2.Id IS NULL
SELECT w.* FROM Words AS w LEFT JOIN Words as w2
ON w.Id=w2.Id AND w.Lang=w2.Lang AND w.Rev<w2.Rev
AND w.Id=527 WHERE w2.Id IS NULL
Thanks,
Csaba Gabor from Vienna
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
