Q:

left join with special condition on right table

don't know if this is possible.. I'm using sqlite3
schema:
CREATE TABLE docs (id integer primary key, name string);
CREATE TABLE revs (id integer primary key, doc_id integer, number integer);
I want to select every job joined with only one of its revisions, the one with the highest number. How can I achieve this?
Right now I'm doing a left join and getting everything and then I'm filtering it in the application, but this sucks..
(by the way, can you suggest me a good and easy introductory book on databases and how they work and maybe something about sql too..)
thanks!

A:

try this
   Select * From docs d
      Join revs r
         On r.doc_id = d.id
   Where r.number = 
         (Select Max(number ) from revs
          Where Doc_Id = d.Id)

or, if you want the docs with no Revs (Is this possible?)
   Select * From docs d
      Left Join revs r
         On r.doc_id = d.id
           And r.number = 
                (Select Max(number ) from revs
                 Where Doc_Id = d.Id)

A:

Not sure if your engine supports this, but typically, you would do something like this in ANSI SQL:
SELECT docs.*
    ,revs.*
FROM docs
INNER /* LEFT works here also if you don't have revs */ JOIN revs
    ON docs.id = revs.doc_id
    AND revs.number IN (
        SELECT MAX(number)
        FROM revs
        WHERE doc_id = docs.id
    )

There are a number of ways to write equivalent queries, using common table expressions, correlated aggregate subqueries, etc.

