By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,378 Members | 1,385 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,378 IT Pros & Developers. It's quick & easy.

(Newbie) Join problem.

P: n/a
Hi,
I'm new to SQL trying to use a base that was set up by another firm.
I can't get it to do what I want and I don't know if it's due to my
ignorance or bad table design ...

I have three tables
Sales
containing ProdID and Amounts

Products
containing ProdID and ProdTypeID and
ProdSubTypeID

Product-types
containing ProdTypeID and ProdTypeIDLabel and
ProdSubTypeID and ProdSubTypeIDLabel

I have to do some stats in Excel or in Crystal
totalling Amounts
grouped by ProductTypeLabel and
grouped by ProductSubTypeLabel.

Here's what I can do...
------------------------------
Using ProdTypeID, I can join Product-Types to Products,
and
using ProdID, I can join Products to Sales
and get a sum of amounts grouped by ProductTypeLabel. OK.

Also,
Using ProdTypeID, I can join Product-Types to Products,
and
using ProdID, I can join Products to Sales
and get a sum of amounts grouped by ProductSubTypeLabel. OK.
Here's what I can't do...
------------------------------
Get both (i.e. ProductTypeLabel and ProductSubTypeLabel) in the same report
and the correct amounts.

I have found a solution but it involves modifying the tables.
I create a new field with the Type and SubType concatenated in Products and
also in Product-Types (ProductTypeGlobal) and then join on that.
So,
Using ProductTypeGlobal, I can join Product-Types to Products,
and
using ProdID, I can join Products to Sales
and get a sum of amounts
grouped by ProductTypeLabel
AND
grouped by ProductSubTypeLabel
OK.

Is there some way to create the correct joins in SQL without creating the
new field?

Thanks

SQL Newbie.


Jun 1 '08 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.