left join with special condition on right table

Question

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!

Answer 1

Here is a very good list of books for Database Design

http://stackoverflow.com/search?q=database+book

Answer 2

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)

Answer 3

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.

Answer 4

select d.*, r.max_number
from docs d
left outer join (
    select doc_id, max(number) as max_number
    from revs
    group by doc_id
) r on d.id = r.doc_id

Answer 5

Database Design : Database Design for Mere Mortals by Hernandez

SQL : The Practical SQL Handbook

If you want to hurt your head, any of the SQL books by Joe Celko.

Answer 6

If every job has revisions (e.g., starting with rev 0), I would use the same approach as OrbMan, but with an inner join. (If you are certain you are looking for a 1-to-1 match. why not let SQL know, too?)

select d.*, r.max_number
from docs d
inner join
(
    select doc_id, max(number) as max_number
    from revs
    group by doc_id
) r on d.id = r.doc_id

Answer 7

I'd recommend "A Sane Approach to Database Design" as an excellent introduction to good design practices. (I am slightly biased. I wrote it. But hey, so far it has a 5-star average review on Amazon, none of which reviews were contributed by me or any friends or relatives.)