|
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
SQL Union question
I'm working in MS Access 97, and have two tables:
Items SKU ......... Description 1001 ......... Apples 1002 ......... Bananas 1003 ..........Prunes 1004 ..........Grapes Sales SKU .......Quantity .......Date 1001 .........5 ...........1/5/2004 1001 .........3 ...........1/8/2004 1002 ........12 ..........1/4/2004 1002 .........8 ...........1/12/2004 1004 ........11 ..........1/25/2004 1004 ......125 ...........2/6/2004 I'm trying (without success) to write a query which will sum the quantity's sold for a specified month. However, I'd also like to see a zero value for any item that wasn't sold. ie: I'd like one row for each item in the Items table. With the above data, the results for January should be: SKU ......Description........ Quantity 1001 ......Apples .................8 1002 ......Bananas .............20 1003 ......Prunes .................0 1004 ......Grapes ................11 I've tried both outer joins and unions, but can't get it to work. Can someone help me? |
|
#2
|
|||
|
|||
|
can u tell me what is the output with outerjoin
|
|
#3
|
||||
|
||||
|
Try something like
Code:
SELECT A.SKU, A.Description, SUM(B.Count) As Quantity FROM Items As A INNER JOIN Sales As B On (A.SKU = B.SKU) ORDER BY A.SKU |
|
#4
|
|||
|
|||
|
both queries produce the same result:
SKU ......Description........ Quantity 1001 ......Apples .................8 1002 ......Bananas .............20 1004 ......Grapes ................11 it's missing this line: 1003 ......Prunes .................0 |
|
#5
|
|||
|
|||
|
turn the inner Join in Memnoch's example to a Left Join
S- |
![]() |
| Viewing: ASP Free Forums > Database > SQL Development > SQL Union question |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|