sql query for getting data from two related tables

Question

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

Answer 1

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