SQL Union question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Union question

Author

Topic

mondo3
Starting Member

13 Posts

Posted - 2004-10-07 : 14:27:14

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

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 Item table.
With the above data, the results 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?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-07 : 14:30:09

Select
A.sku,
A.description,
Quantity = sum(isnull(quantity,0))
From Items as A
Left Join Sales as B
On A.sku = B.sku
Where B.date between '1/1/2004' and '1/31/2004'
Group By A.sku, A.description

EDIT:Ooops... Access, try the following too

Select
Items.sku,
Sales.description,
sum(iif(isnull(quantity),0,quantity)) as TotalQty
From Items
Left Join Sales
On Items.sku = Sales.sku
Where Sales.date between '1/1/2004' and '1/31/2004'
Group By Items.sku, Items.description

Corey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-07 : 14:46:02

Come on, Corey ! a left outer join, and then a WHERE clause on the outer table? you know you can't do that!

it becomes an INNER JOIN.

in Access, create one query that gives you total sales per item for the date range you like. Then create another query that pulls from the Items table and does a LEFT OUTER JOIN to the first query you created.

- Jeff

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-10-07 : 14:47:34

quote:
Originally posted by jsmith8858

you know you can't do that!

I think he just did

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-10-07 : 15:02:23

quote:
Originally posted by jsmith8858

Come on, Corey ! a left outer join, and then a WHERE clause on the outer table? you know you can't do that! it becomes an INNER JOIN.

Funny you should say that: one of my cow-orkers was trying to do something sort of similar using Enterprise Manager to build the view and discovered that it moves such WHERE conditions into the join's ON condition of its own accord

Which might have been a good idea, except that the condition was a <> rather than an =

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-07 : 15:41:39

Select
Items.sku,
Items.description,
sum(nz(quantity,0)) as TotalQty
From Items
Left Join
[select sku,Quantity, Date
from Sales
Where Sales.date between '#1/1/2004#' and '#1/31/2004#'] as s
On Items.sku = s.sku
Group By Items.sku, Items.description

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-07 : 16:07:02

quote:
Originally posted by VIG

Select
Items.sku,
Items.description,
sum(nz(quantity,0)) as TotalQty
From Items
Left Join
[select sku,Quantity, Date
from Sales
Where Sales.date between '#1/1/2004#' and '#1/31/2004#'] as s
On Items.sku = s.sku
Group By Items.sku, Items.description

Yeah... this is what I meant

Corey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-07 : 16:32:44

quote:
Originally posted by Arnold Fribble

quote:
Originally posted by jsmith8858

Come on, Corey ! a left outer join, and then a WHERE clause on the outer table? you know you can't do that! it becomes an INNER JOIN.

Funny you should say that: one of my cow-orkers was trying to do something sort of similar using Enterprise Manager to build the view and discovered that it moves such WHERE conditions into the join's ON condition of its own accord
Which might have been a good idea, except that the condition was a <> rather than an =

- Jeff

mondo3
Starting Member

13 Posts

Posted - 2004-10-08 : 09:50:28

seventhnight: the query becomes an inner join, so it didnt work.
VIG: I get the error "syntax error in from clause" when i try your code

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-08 : 09:53:13

mondo -- did you understand/try what I suggested? that's your answer.

in Access 97, you need to do this using two queries. One query does the grouping and the filtering for the range you need. Your final query then LEFT JOINS your Items table to the summarized data in your first query.

- Jeff

mondo3
Starting Member

13 Posts

Posted - 2004-10-08 : 10:21:16

I sort of understand, but keep getting an error in the from clause. Is this what you mean:

PARAMETERS [txtbegdate] DateTime,[txtenddate] DateTime;
select * from items left join
(
select sales.sku ,sum(quantity) as TotalQty
from sales
Where sales.date Between txtbegdate And txtenddate
group by sales.sku
)

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-08 : 10:29:09

mondo3
Sorry, I can't check it now.
try [ ] (may be ]. ) instead of () and also put alias after "]"
left loin
[ ] as s
or
left loin
[ ]. as s

mondo3
Starting Member

13 Posts

Posted - 2004-10-08 : 10:38:57

the brackets don't appear to be the problem. anyone?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-08 : 10:45:39

Again: YOU NEED TO CREATE TWO (2) SEPARATE QUERIES.

Access 97 does not support subqueries in the FROM clause unless you save them as separate queries.

Query1: Select ....

Query2: Select .. from .. left outer join Query 1 on ...

- Jeff

mondo3
Starting Member

13 Posts

Posted - 2004-10-08 : 11:13:35

Two separate queries...are you saying that i have to create a table to save the results of the first query, and then run the 2nd query on that table?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-08 : 11:19:24

Mondo --

No -- create TWO queries. Not another table. You have the tables you need. You need to do this in two steps. 1 query gathers the data, does the filtering and the group by. you write, run it, test it, save it. But it doesn't include all items, right?

So you create ANOTHER query, which queries the first one along with the Items table and does a LEFT JOIN between the two.

You are in Access 97, right?

YOu know how to create a query, right? Click on "New" and then "Design" view, add some tables to it, and then fill in the grid. Then click "SAVE" and give it a name. call it "Query1". you have just created a query.

Now close the query completely, and create a NEW query again. Don't open the old one, create a NEW query. Click NEW and then go to the Design view. Now in the NEW query (not the first one), add in the Items table. Notice you can not only add Tables to your query, but also other queries! Use this feature to add in Query1 (the first one you created). Do the LEFT JOIN from the Items table to Query1, add fields, design it the way you need, and now save it. Call it Query 2. close everything up.

YOu now have two queries in your database. One is called QUery1, the other is called Query2. If you don't have that, go back and start over.

Query2 calls Query1 just the way a subquery is called in the FROM expressions that you tried.

Does this make any sense at all? You may need to do some googling on Access and queries.

- Jeff

mondo3
Starting Member

13 Posts

Posted - 2004-10-08 : 11:33:10

Holy cow...that worked! The main concept that I was missing is that you can have one query query a 2nd query.
Thanks for everyone's help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources