From: Robert Schaaf <ruby-bucket@...>
Date: 2009-06-08T21:07:51+09:00
Subject: Re: sqlite3 & joins

I don't use SQLite, I'm more of a SQl heavy (DB2, Postgresql, Oracle)  
type.

What you're asking for is a plain old, b-flat, vanilla join. I don't  
know why you'd be doing this in three separate queries.

Without seeing your schema, or knowing exactly which columns you want  
to pull, I'll use '*', which cats all columns in all tables.  I  
assuming you have a part_number in parts (or maybe a part_name.)

Select *
   from customer, jobs, parts
  where customer.customer_number = jobs.customer_number
    and jobs.job_number = parts.job_number
  order by customer.customer_number,
           jobs.job_number,
           parts.part_number

This should be quite efficient if there are indexes on  
jobs.customer_number and parts.job_number, otherwise a sequential scan  
will be needed for each of the two joins, and that will kill you.

> but really I'm more concerned with getting everything playing in tune
> and then when I'm more familiar wit SQLite look at optimizations like
> using joins.

I suggest you do the hard work  up front.  It will save you a lot of  
bad design decisions down the line.

Good luck,

Bob Schaaf


On Jun 8, 2009, at 7:28 AM, Dave Lilley wrote:

> this question goes on from my previous one about my 1st hurdle using
> SQLite3.
>
> what sort of performance hit will you get if you have 3 select
> statements (1 for each table/DB file)?
>
> tables are customer, Jobs, parts.
>
> customer has a unique field called customer number.
>
> Jobs has a field for the customer number in it and a unique field  
> called
> Job number.
>
> lastly parts has a field called job number.
>
> not sure how many rows there might be for a customer and rows for  
> parts
> at present.
>
> but really I'm more concerned with getting everything playing in tune
> and then when I'm more familiar wit SQLite look at optimizations like
> using joins.
> an yes ultimately i may move off sqlite to another DB but that's  
> another
> step.
>
> Oh also all this is via DBI.
>
> Active record will be another stepping stone (Feel like I've got too
> much to cope with for now but perhaps ultimatly go to active record
> too).
>
> cheers,
>
> dave.
> -- 
> Posted via http://www.ruby-forum.com/.
>