Problem with joins in SQL.

[SQL-Newbie]

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.

[Plamen Ratchev]

It is unclear for your description what are the correct relations between
entities in your tables, but here is something to try:

SELECT T.ProdTypeIDLabel,
T.ProdSubTypeIDLabel,
SUM(Amount) AS amount
FROM ProductTypes AS T
JOIN Products AS P
ON T.ProdTypeID = P.ProdTypeID
AND T.ProdSubTypeID = P.ProdSubTypeID
JOIN Sales AS S
ON S.ProdID = P.ProdID
GROUP BY T.ProdTypeIDLabel, T.ProdSubTypeIDLabel;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[SQL-Newbie]

Thanks,
will give this a blast on Monday.

Sql N.

"Plamen Ratchev" <Pl****@SQLStudio.coma écrit dans le message de news:
YY******************************@speakeasy.net...

It is unclear for your description what are the correct relations between
entities in your tables, but here is something to try:

SELECT T.ProdTypeIDLabel,
T.ProdSubTypeIDLabel,
SUM(Amount) AS amount
FROM ProductTypes AS T
JOIN Products AS P
ON T.ProdTypeID = P.ProdTypeID
AND T.ProdSubTypeID = P.ProdSubTypeID
JOIN Sales AS S
ON S.ProdID = P.ProdID
GROUP BY T.ProdTypeIDLabel, T.ProdSubTypeIDLabel;

HTH,

Plamen Ratchev
http://www.SQLStudio.com