troubleshooting Question

Query Issue

Avatar of minglelinch
minglelinch asked on
Microsoft SQL Server 2008SSRS
10 Comments3 Solutions343 ViewsLast Modified:
I need a help for a query which need to list each type of order for every person, and I want quatity value as 0 for the type that is not ordered.  

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          Joe             2        4              ...         ...
003          Joe             4        3               ...         ...
004          Joe             5        2               MO        ...
004          Mike           3        30                ...         ...
005          Mike           4        40               ...         ...
006          Mike           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 quantity for that type for the person (0 for not ordered item). No SUM or COUNT needed. I appreciate any help.

ordername     location     tid     Quantity
Jeo                 ...               1        5
Jeo                 ...               2        4
Jeo                 ...               3        0                -- 0 here
Jeo                 ...               4        3
Jeo                 ...               5        2
Mike               ...               1        0                -- 0 here
Mike               ...               2        0                -- 0 here
Mike               ...               3        30
Mike               ...               4        40
Mike               ...               5        20
 ...               ...      ...
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 3 Answers and 10 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros