replacing a programming loop with the correct JOIN

Please help a programmer do this in a more SQL-like manner.

I have database with 4 tables related in a sort of tree: progs,
events, questions, answers. Each row in each table has a unique ID:
prog_id, event_id, question_id,answer_id. Each row in the bottom
three tables (not progs) knows the id of the table above it. For
example, every answer knows the question_id it associates with, and
every question knows the event_id it associates with.

The programmer in me selects all the categories and loops through the
results. For example, for each prog I select all the events and loop
through them to find the questions, etc. See the issue? With the
proper join I should be able to select all the information at once,
one row for every row in the bottom 'answers' table.

There are additional conditions, simulated by 'where
progs.status="initializing" '

This is the select I came up with:

select progs.title, progs.prog_id, events.title, events.event_id,
questions.content, questions.question_id, answers.content,
answers.answer_id from progs INNER JOIN (events,questions,answers) on
(progs.prog_id=events.prog_id AND events.event_id=questions.event_id
AND questions.question_id=answers.question_id) where
progs.status="initializing" order by
progs.prog_id,events.event_id,questions.question_i d,answers.answer_id;

can one of y'all gurus advise me whether this will always give me one
line for each answer, provided the relationships are correct?

Thanx!
-- clh

Thanx!


> It looks like it, yes.
>
> --
> Premature optimization is the root of all evil.
> -- Sir Tony Hoare

oops -- this fails if there is a question with no associated answer,
event with no associated question, prog with no associated event. Is
there an improvement of this select that will give me a row for
conditions where the branches of the tree are not populated?

Thanx!

On Feb 17, 2:04 pm, christop...@dailycrossword.com wrote:
> Thanx!
>
> > It looks like it, yes.
>
> > --
> > Premature optimization is the root of all evil.
> > -- Sir Tony Hoare

Thanx peter --
I am trying to optimize too early, I think.. I can forsee several
instances where the table will contain unreferenced data -- for
example if something is inadvertantly deleted. If I delete the
eintire tree and the user did not intend that (even after
confirmation) that would be bad. OTOH if I leave the unreferenced
data in the table for a manual rebuild, then it clutters up these
fancy joins I am trying to devise.

Better if I go with my first solution and walk through the table with
the software -- probably take 0.08 secs or something.

Thanx for your help!
-- clh

On Feb 18, 5:27 pm, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> On 17 Feb 2007 17:40:43 -0800, christop...@dailycrossword.com wrote:
>
> > On Feb 17, 2:04 pm, christop...@dailycrossword.com wrote:
> >> Thanx!
>
> >> > It looks like it, yes.
>
> >> > --
> >> > Premature optimization is the root of all evil.
> >> > -- Sir Tony Hoare
>
> > oops -- this fails if there is a question with no associated answer,
> > event with no associated question, prog with no associated event. Is
> > there an improvement of this select that will give me a row for
> > conditions where the branches of the tree are not populated?
>
> Well, it meets the original condition, which talked specifically about
> presenting every answer, provided the relationships were correct. "No
> answer" wasn't talked about. (:
>
> Quick answer is that if you have some things for which there are no
> dependant entities and you still want to see it all, use OUTER JOIN
> instead of INNER JOIN. The places where there are missing entities will
> show up as null. Note that this these are not the same as "".
>
> --
> 16 megs in a '95 box! Yo Ho Ho and a battle of RAM!

wow -- hou have been amazingly helpful -- thanx
I told my new boss this project requires a database expert from the
get-go, but he wants me to do it all. I have a dozen notes in my code
(there *must* be a better way to do this). I know we will be so far
into this project before we hire an expert that she / he will just
shake her / his head in disbelief and double her / his fee *grin*.
For now I am going to have to take the simplist 'kluge' solution and
hope for the best.

Are you available to consult if he changes his mind?

-- clh

On Feb 19, 5:43 pm, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> On 19 Feb 2007 16:59:46 -0800, christop...@dailycrossword.com wrote:
>
> > Thanx peter --
> > I am trying to optimize too early, I think.. I can forsee several
> > instances where the table will contain unreferenced data -- for
> > example if something is inadvertantly deleted. If I delete the
> > eintire tree and the user did not intend that (even after
> > confirmation) that would be bad. OTOH if I leave the unreferenced
> > data in the table for a manual rebuild, then it clutters up these
> > fancy joins I am trying to devise.
>
> This is the thing for which constraints were invented. They'll prevent
> orphaned items and the like, by either cascading deletes of parent items
> (and deleting all the dependant child items) or restricting deletes of
> items with dependants from happening in the first place.
>
> http://dev.mysql.com/doc/refman/5.0/...ey-constraints...
>
> > Better if I go with my first solution and walk through the table with
> > the software -- probably take 0.08 secs or something.
>
> Read the page above and mull it for a day or two. You might change your
> mind back again.
>
> --
> 93. If I decide to hold a double execution of the hero and an underling who
> failed or betrayed me, I will see to it that the hero is scheduled to go
> first.
> --Peter Anspach's list of things to do as an Evil Overlord

On Feb 23, 11:36 am, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> On 22 Feb 2007 13:40:36 -0800, christop...@dailycrossword.com wrote:
>
> > wow -- hou have been amazingly helpful -- thanx
> > I told my new boss this project requires a database expert from the
> > get-go, but he wants me to do it all. I have a dozen notes in my code
> > (there *must* be a better way to do this). I know we will be so far
> > into this project before we hire an expert that she / he will just
> > shake her / his head in disbelief and double her / his fee *grin*.
> > For now I am going to have to take the simplist 'kluge' solution and
> > hope for the best.
>
> > Are you available to consult if he changes his mind?
>
> *grin* Before or after the fee-doubling?

I talked to him today and he is very open to me having someone to
consult on some of the broader issues. email me here:
christopherhannah--at--yahoo.com and I'll give you my real email
address.
cheers!
-- clh