Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2013
    Posts
    13

    Question Unanswered: Need help writing query

    Hi all, i am stuck with a query and would love to receive some help from the experts outhere

    I have a table that stores events information and each event can be child of other event, for example


    eventId | eventName | eventParent
    --------------------------------------
    1 | Cool Event | 0
    2 | Other event | 0
    3 | Sub-event a | 1
    4 | Sub-event b | 1


    i would like to have an array with event ids as keys and count of child events as values, for the example above it would be:

    1 => "2"
    2 => "0"
    3 => "0"
    4 => "0"


    Currently i do this using php, first query for all event ids and put them in an array, then go trough the array with a foreach and query for the COUNT of the event childs.
    The problem is that this is sending too much queries to the db as each event would require one query just to count childs..

    Any help creating a query that will return the specified array would be much appreciated

    Thanks in advance,
    Gaston

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As long as you only need to go one generation into the tree (like in your example), a subquery or a simple JOIN and a Count(1) will do nicely.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2013
    Posts
    13
    Hello PatP, thanks for taking the time to answer.
    Could you please help me some more, i am not really sure how to do a JOIN with only one table.

    I tried the following but didn't work as desired

    SELECT parent.event_id, COUNT(child.event_id)
    FROM events AS parent, events AS child
    WHERE child.event_parent=parent.event_id

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You are definitely on the right track, but we need to adjust a few details. What results did that query produce? Look up LEFT JOIN, that's a key to getting what you need.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jan 2013
    Posts
    13
    Thanks for the advice Pat.

    The query i posted only returned one event_id and the total amount of child events.

    I tried with

    SELECT parent.event_id, COUNT(child.event_id)
    FROM events AS parent LEFT JOIN events AS child ON child.event_parent = parent.event_id

    and

    SELECT events.event_id, COUNT(child.event_id)
    FROM events LEFT JOIN events AS child ON child.event_parent =events.event_id

    but both retuned and empty array..
    Sorry if these queries don't make any sense, i am bit confused on how to join the count to the other table

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try:
    Code:
    SELECT parent.event_id, COUNT(child.event_id)
       FROM events AS parent
       LEFT JOIN events AS child
          ON child.event_parent = parent.event_id
       GROUP BY parent.event_id;
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jan 2013
    Posts
    13
    That worked perfectly, thanks so much for your help! you just made my day

Posting Permissions

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