Professional Web Applications Themes

replacing a programming loop with the correct JOIN - MySQL

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 ...

  1. #1

    Default 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

    christopher@dailycrossword.com Guest

  2. #2

    Default Re: replacing a programming loop with the correct JOIN

    On 16 Feb 2007 15:20:16 -0800, com wrote: 

    It looks like it, yes.

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

  3. #3

    Default Re: replacing a programming loop with the correct JOIN

    Thanx!

     


    christopher@dailycrossword.com Guest

  4. #4

    Default Re: replacing a programming loop with the correct JOIN

    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, com wrote: 
    > [/ref]


    christopher@dailycrossword.com Guest

  5. #5

    Default Re: replacing a programming loop with the correct JOIN

    On 17 Feb 2007 17:40:43 -0800, com wrote: 
    >> [/ref]
    >
    > 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?[/ref]

    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!
    Peter Guest

  6. #6

    Default Re: replacing a programming loop with the correct JOIN

    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" <com> wrote: [/ref]
    > [/ref]
    > [/ref]

    >
    > 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![/ref]


    christopher@dailycrossword.com Guest

  7. #7

    Default Re: replacing a programming loop with the correct JOIN

    On 19 Feb 2007 16:59:46 -0800, com wrote: 

    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/en/innodb-foreign-key-constraints.html
     

    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
    Peter Guest

  8. #8

    Default Re: replacing a programming loop with the correct JOIN

    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" <com> wrote: 
    >
    > 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/en/innodb-foreign-key-constraints...

    >
    > 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[/ref]


    christopher@dailycrossword.com Guest

  9. #9

    Default Re: replacing a programming loop with the correct JOIN

    On 22 Feb 2007 13:40:36 -0800, com wrote: 

    *grin* Before or after the fee-doubling?

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

  10. #10

    Default Re: replacing a programming loop with the correct JOIN

    On Feb 23, 11:36 am, "Peter H. Coffin" <com> wrote: 

    >
    > *grin* Before or after the fee-doubling?[/ref]

    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-- and I'll give you my real email
    address.
    cheers!
    -- clh

    christopher@dailycrossword.com Guest

Similar Threads

  1. Replies: 2
    Last Post: September 18th, 09:59 PM
  2. Replies: 0
    Last Post: May 5th, 06:41 PM
  3. outer join, cross join, union? DDL/sample data incl.
    By Kevin in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 5th, 08:01 PM
  4. Rewrite Query using Inner Join, Left Join e.t.c
    By Vishal in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 23rd, 08:02 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139