Need help with data arrays

Sorry to be a pain . . . 

If the children table had a toytype filed then I need to separately SUM children.toys where toytype =1 and another SUM for where toytype = 2

How can I do that? Is it possible in one query?

Hey, no problem. The forum is to ask questions. Anyway, yes, you can do that with one query. Suppose you have tables like this.

Code:

Table: parents
id  name
1  Smith, Joe
2  Doe, Mary
3  Chang, Tony
4  Martinez, R
5  Kramer, Helen
6  Green, Robert

Table: children
id  parents  name     toytype  toycount
1     1     Kimmie      1        3
2     1     Kimmie      2        4
3     1     Josh        1        1
4     1     Josh        2        7
5     1     Betty       1        3
6     2     Jacob       1        4
7     2     Jacob       1        1
8     2     Marnie      2       17
9     2     Taylor      1        8
10     2     Taylor      2        3
11     3     Martin      1        3
12     3     Martin      2        8
13     3     Tony        1        0
14     3     Tony        2        0
15     4     Hailey      1       13
16     6     Barbara     1        3
17     6     Freddie     1        1
18     6     Freddie     2        2
19     6     Suzy        1        7
20     6     Sophie      2        8

Here's the query.

Code:

SELECT
  parents.id AS id,
  parents.name AS name,
  children.toytype AS ttype,
  SUM(children.toycount) AS numtoys
FROM parents
INNER JOIN children
ON parents.id = children.parents
GROUP BY ttype, id
ORDER BY id, ttype

The key is the GROUP BY clause. It affects how the SUM() is done. If you just grouped by ttype, you'd get two lines, one with the grand total of all type 1 toys, and the other with the grand total of all type 2 toys. If you just grouped by id (the parent's id), you'd get a line for each parent with the total sum of toys of both types. When you group it first by ttype and then by id, however, you get this.

Code:

id  name           ttype  numtoys
1  Smith, Joe       1       7
1  Smith, Joe       2      11
2  Doe, Mary        1      13
2  Doe, Mary        2      20
3  Chang, Tony      1       3
3  Chang, Tony      2       8
4  Martinez, R      1      13
6  Green, Robert    1      11
6  Green, Robert    2      10

The ORDER BY clause sorts the results to make sure the rows are sorted by parent id. You could also order by parents.name or any other field.

What is your MySQL setup? I really suggest you get phpMyAdmin, which lets you test different queries. There's an actual free desktop application out there called MySQL Admin, but it is very sluggish, and I found it hard to understand.
Consider also getting an SQL book. I like "Simply SQL," by Rudy Limeback. It's $9.99 on Kindle, and you can also get it used. The book really helped me understand joins.

Thanks again for coming back and explaining clearly. I've got phpmyadmin and have been using it most of the day to try to get what I want from my database. I don't think I was very good at explaining the nature of the problem. I'm building a staff absence tracking application so  Ihave multiple staff and multiple absence 'types' the 'parent' table is staff and the 'child' table is events ... therecan be multiple different absence types in in my child table and I only need totals of two of them ... I also have to select events that occur in a date range. However, the good news for me is that I've come up with a solution (inspired by your previous post) and here it is:

Code:

$SQLstring="SELECT *
FROM staff t LEFT JOIN
(
  SELECT events.staffid, SUM(amount) total_absence
    FROM events
   WHERE events.typeid = 1 and events.date between '{$this->session->userdata('leave_start')}' and '{$this->session->userdata('leave_end')}'  
  GROUP BY events.staffid
) a
  ON t.staffid = a.staffid LEFT JOIN
(
  SELECT events.staffid, SUM(amount) total_sickness
    FROM events
   WHERE events.typeid = 2 and events.date between '{$this->session->userdata('leave_start')}' and '{$this->session->userdata('leave_end')}'    
  GROUP BY events.staffid
) s
  ON t.staffid = s.staffid
JOIN department ON t.deptid = department.deptid";

This gives me to total of event type 1 and the total of event type 2 between the relevant dates for each member of staff. The result set contains the staffid 3 times (once from the SELECT * and once each because of the select in the subqueries) and I don't know how to get rid of the two 'extra' ones. Also, if there are no records of the specified type in the date range for any staff member the the totals return as NULL ... is there a way to get the query to return 0 rather than NULL?

also, is this solution a good one or could I have done better?