sql query for getting data from two related tables

I have two tables, employee and inventory. One employee can have zero or more inventories. I would like to list employee information along with at most one inventory information开发者_C百科 and count of inventories belongs to one employee.

employee table

emp_num  last_name  first_name
-----------------------------------
100      john       smith

101      mike       pet

102      jes        lyoid

inventory table

inv_num  emp_num
---------------------------
12       100

13       100

15       100

30       102

desired Output

emp_num     last_name       invnum  count(inv_num)
-------------------------------------------------------------------------- 
100         john            12      3

101         mike            -       0

102         jes             30      1

What sql query can I use in this case?

---

Try this:

SELECT emp_num, last_name, MAX(inv_num) AS invnum, COUNT(inv_num) AS inv_count
FROM employee e LEFT OUTER JOIN inventory i ON e.emp_num = i.emp_num
GROUP BY e.emp_num, e.last_name

---

You could do something like this

Select E.Emp_Num,
       e.Last_name,
       MIN(Inv_Num) AS OldestInv,
       COUNT(Inv_Num) AS TotalInv
FROM Employee E
    LEFT OUTER JOIN Inventory I
        (E.Emp_Num = I.Emp_Num)
GROUP BY E.Emp_Num, E.Last_Name

This will give you the minimum invoice number and the total count. The left outer join is the key

---

SELECT 
    e.emp_num,
    e.last_name,
    IFNULL(MAX(i.inv_num),'-') AS 'invnum',
    COUNT(i.inv_num) AS 'count(inv_num)'
FROM
    employee e LEFT JOIN inventory i
    ON e.emp_num = i.emp_num
GROUP BY
    e.emp_num, e.last_name