# Sqlite3 & joins

**URL:** <https://www.ruby-forum.com/t/sqlite3-joins/169767>\
**Category:** Ruby\
**Created:** [June 8, 2009, 1:28pm UTC](https://www.ruby-forum.com/t/sqlite3-joins/169767 "2009-06-08T13:28:05Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![paradise](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/8e7dd6/32.png) [@paradise](https://www.ruby-forum.com/u/paradise)\
**Post date:** [June 8, 2009, 1:28pm UTC](https://www.ruby-forum.com/t/sqlite3-joins/169767/1 "2009-06-08T13:28:05Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![paradise](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/8e7dd6/32.png) [@paradise](https://www.ruby-forum.com/u/paradise)\
**Post date:** [June 8, 2009, 2:04pm UTC](https://www.ruby-forum.com/t/sqlite3-joins/169767/2 "2009-06-08T14:04:45Z")

</div>

I would always recommend using the database to perform joins. People  
have spent thousands of hours (perhaps incorporating the work of  
millions of other peoples’ hours) in designing the database to optimise  
joins, so why would you possibly want to reinvent that wheel?

From what I’ve seen of ActiveRecord, it has some way of expressing  
relationships but I’m a bit dubious about the whole object-relation  
mapping model. Database-centric approaches have worked for big complex  
systems for decades so why force the issue.

---

<div class="post-metadata">

**Author:** ![paradise](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/8e7dd6/32.png) [@paradise](https://www.ruby-forum.com/u/paradise)\
**Post date:** [June 8, 2009, 2:08pm UTC](https://www.ruby-forum.com/t/sqlite3-joins/169767/3 "2009-06-08T14:08:56Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![paradise](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/8e7dd6/32.png) [@paradise](https://www.ruby-forum.com/u/paradise)\
**Post date:** [June 8, 2009, 3:23pm UTC](https://www.ruby-forum.com/t/sqlite3-joins/169767/4 "2009-06-08T15:23:40Z")

</div>

On second thought, it doesn’t look like your schema does what it ought  
to.

You’d think that any customer can contract for a certain job which  
requires specific parts which can be used in other jobs.

If so, you’d need

1. a customer table with unique records for each customer, identified  
by customer\_number;

2. a jobs table describing a job, with a unique key for job\_number;

3. a parts table describing a part, with a unique key for part\_number;

THEN you need the join tables.

1. a contracts table, with a unique key on contract\_number, and an  
index on customer\_number;

2. a bill\_of\_parts table, indexed uniquely on job\_number.

Then the join would be

customers.customer\_number = contracts.cusomer\_number  
contracts.job\_number = jobs.job\_number  
jobs.job\_number = bill\_of\_parts.job\_number  
bill\_of\_parts.part\_number = parts.part\_number

This too is trivial, and should be quick if your RDB isn’t a complete  
dog. I don’t know of SQLite permits multi-column keys and indexes, so  
perhaps, “Woof, woof!”

BS