a query problem with join

I'm writing a query need to list each type of order for every person, including a total for each type category, and I want a total 0 for the type that is not ordered. It seems not hard but is really not easy.

I have used full outer join two sets/tables, but the type which is not ordered won't show.
I also used cross join, the type which is not ordered will show but with too many confusioning rows.


OrderTable
orderid  ordername   tid  quantity   location  ordertime
001          Joe             1        5               NY         ...
002          Anne           4       9                ...         ...
003         Joe              2       20               ...         ...
004          Joe             2       30               MO        ...
004          Mike           3        9                ...         ...
005          Joe             5       40               ...         ...
006          Joe             5       20               ...         ...


TypeTable
tid    type
1       type1
2       type2
3       type3
4       type4
5       type5

I need to make a query to generate the following, listing every person name, some info, typeid and total quality for that type for the person (0 for not ordered item).
I need help writing this query. I appreciate.

ordername     location     tid     count
Jeo                 ...               1        5
Jeo                 ...               2        50
Jeo                 ...               3        0
Jeo                 ...               4        0
Jeo                 ...               5        60
Anne               ...               1        0
Anne               ...               2        0
Anne               ...               3        0
Anne               ...               4        9
Anne               ...               5        0
Mike               ...               ...      ...
LVL 1
minglelinchAsked:
Who is Participating?
 
ArgentiCommented:
And this one will give you ALL ordernames, with ALL types of orders, counting how many orders of each type, with sum over quantities, by each type:
select N.ordername, T.type, 
   count(O.orderid) as NumberOfOrders, isnull(sum(O.quantity), 0) as TotalQuantity 
from 
(select distinct ordername from OrderTable) N
cross join TypeTable T
left join OrderTable O on (N.ordername = O.ordername and T.tid = O.tid)
group by N.ordername, T.type

Open in new window

0
 
ArgentiCommented:
This will give you ALL types of orders (without orders) and the orders for each type:
select * 
from TypeTable T
left join OrderTable O on (O.tid = T.tid)
order by T.tid, T.type, O.ordername

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Argenti,

That won't work, as the outer join will not retrieve all permutations - if a type is not used in OrderTable, all corresponding columns are empty instead of containing data. And I don't see the sum/grouping here ...
0
 
minglelinchAuthor Commented:
Thanks for the answer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.