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 ProdSubTypeIDLa bel



I have to do some stats in Excel or in Crystal

totalling Amounts

grouped by ProductTypeLabe l and

grouped by ProductSubTypeL abel.



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 ProductTypeLabe l. 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 ProductSubTypeL abel. OK.





Here's what I can't do...

------------------------------

Get both (i.e. ProductTypeLabe l and ProductSubTypeL abel) 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 (ProductTypeGlo bal) and then join on that.

So,

Using ProductTypeGlob al, I can join Product-Types to Products,

and

using ProdID, I can join Products to Sales

and get a sum of amounts

grouped by ProductTypeLabe l

AND

grouped by ProductSubTypeL abel

OK.



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



Thanks



SQL Newbie.

[Plamen Ratchev]

Re: Problem with joins in SQL.

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

SELECT T.ProdTypeIDLab el,
T.ProdSubTypeID Label,
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.ProdTypeIDLab el, T.ProdSubTypeID Label;

HTH,

Plamen Ratchev

http://www.SQLStudio.com

[SQL-Newbie]

Re: Problem with joins in SQL.

Thanks,
will give this a blast on Monday.

Sql N.

"Plamen Ratchev" <Plamen@SQLStud io.coma écrit dans le message de news:
YYqdnaoGjfhpAt_ VnZ2dnUVZ_jqdnZ 2d@speakeasy.ne t...

It is unclear for your description what are the correct relations between
entities in your tables, but here is something to try:
>
SELECT T.ProdTypeIDLab el,
T.ProdSubTypeID Label,
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.ProdTypeIDLab el, T.ProdSubTypeID Label;
>
HTH,
>
Plamen Ratchev
http://www.SQLStudio.com