mysql join tables query I have 2 tables job and dn (layout  below) and would like to join the tables 

if i use

SELECT  part, description ,
        GROUP_CONCAT(job_number ORDER BY job_number desc) AS job_numbers,
        SUM(qty_ordered) as qty_ordered FROM  job
    where  type = 'part'
    GROUP BY  part


I get (part,description,job_numbers,qty_ordered) but would like to join tables to include qty del and delivery note numbers as output (below).  how would I do this

thanks


Job
+---------+---------+-----+----------+-----+-----+----------+-----+
|joblineid|jobitemid| type|job_number|part |descr|qty_ordred|note |
+---------+---------+-----+----------+-----+-----+----------+-----+
| 1       | 10      | part| 1        |part1|desc1|     33   |null |
| 2       | 11      | part| 1        |part2|desc2|     566  |null |
| 3       | 20      | part| 2        |part1|desc1|     423  |null |
| 4       | 21      | part| 2        |part2|desc2|     777  |null |
| 5       | 22      | part| 2        |part3|desc3|     6666 |null |
| 6       | 30      | part| 3        |part4|desc4|     1000 |null |
| 7       | 31      | note| 3        |null |null |     null |anote|
+---------+---------+-----+----------+-----+-----+----------+-----+  


dn
+--------+---------+----+----------+--------------+-----+-----+-------+
|dnlineid|jobitemid|type|job_number|delnote_number|part |descr|qty_del| 
+--------+---------+----+----------+-- -----------+-----+-----+-------+
| 1      |  10     |part| 1        | 1            |part1|desc1|30     |     
| 2      |  20     |part| 2        | 2            |part1|desc1|16     |    
| 3      |  21     |part| 2        | 3            |part2|desc2|800    |     
| 4      |  22     |part| 2        | 4            |part3|desc3|6000   |     
| 5      |  10     |part| 1        | 5            |part1|desc1|500    |    
+--------+---------+----+----------+--------------+-----+-------+-----+  




output
+-----+--------+-------------+--------------+--------------+----------+
|part | desc   | job_numbers | delnote_num | qty_ordred   | qty_del  | 
+-----+--------+-------------+--------------+--------------+----------+
|part1| desc1  | 1,2         |   1,2,5      |     456      |   546    |   
|part2| desc2  | 1,2         |   3          |     1343     |   800    |
|part3| desc3  | 2           |   4          |     6666     |   6000   |
|part4| desc4  | 3           |   null       |     1000     |   null   |
+-----+--------+-------------+--------------+--------------+----------+
Here's one approach:

SELECT  a.part,
        a.description AS "desc",
        a.job_numbers
        dn.delnote_number,
        a.qty_ordered,
        dn.qty_del
    FROM ( SELECT  part, description, jobitemid
                GROUP_CONCAT(job_number ORDER BY job_number desc) AS job_numbers,
                SUM(qty_ordered) as qty_ordered FROM  job
            where  type = 'part'
            GROUP BY  part
         ) AS a
    LEFT JOIN dn  USING(jobitemid)


OP says that this is the result:

part        desc     job_numbers    delnote_num     qty_ordered      qty_del

"part1"     "desc1"    "2,1"       "1"              "456"            "30"
"part3"     "desc3"    "2"         "4"              "6666"           "6000"
"part1"     "desc1"    "2,1"       "5"              "456"            "500"
"part2"     "desc2"    "2,1"       NULL             "1343"           NULL
"part4"     "desc4"    "3"         NULL             "1000"           NULL