SEO

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

On 16 Feb 2007 15:20:16 -0800, christopher@dailycrossword.com wrote:
> 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?

It looks like it, yes.

--
Premature optimization is the root of all evil.
-- Sir Tony Hoare

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

On 17 Feb 2007 17:40:43 -0800, christopher@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!

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!

On 19 Feb 2007 16:59:46 -0800, christopher@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/...nstraints.html

> 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

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 22 Feb 2007 13:40:36 -0800, christopher@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?

--
The plural of datum is not "facts".
A collection of facts is not "knowledge".

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