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. 2 1392
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Marek Kotowski |
last post by:
In MySQL online documentation there are some examples with
multi-tables left joins. But all of them are like this (taken from the
documentation):
SELECT ...
FROM table1
LEFT JOIN table2 on...
|
by: Prem |
last post by:
Hi All Database Gurus,
I am trying to write code which will produce all the possible valid
queries, given tables and join information for tables.
Right now i am just trying to construct all the...
|
by: Keith |
last post by:
I am having a problem creating a many-to-many-to-many type relationship. It
works fine, but when I create a view to query it and test it, it does not
generate the results I expected.
Below...
|
by: Matt |
last post by:
Hello
I have to tables ar and arb, ar holds articles and a swedish
description, arb holds descriptions in other languages.
I want to retreive all articles that match a criteria from ar and...
|
by: Il Khan |
last post by:
Hi, I have an asp application running on a dozen of winXP machines
configured as kiosks. This application stores its data on a MS access db. I
have the need to make some reports extracting data...
|
by: michaelnewport |
last post by:
Greetings,
I like to write my inner joins as below,
but someone at work tells me its not as 'performant'
as using the 'inner join' statement.
Is this true ?
Is there a better way to write it...
|
by: imranpariyani |
last post by:
Hi
i have a severe performance problem with one of my views which has 6 to 8 joins .. any help will be appreciated..
the view is:
CREATE OR REPLACE VIEW thsn.trade_view AS
SELECT...
|
by: MP |
last post by:
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source="...
|
by: Ana RM |
last post by:
Mark.Powell@eds.com (Mark D Powell) wrote in message news:<2687bb95.0308010642.1fc4ff1f@posting.google.com>...
Hi Mark,
Thanks por answer me. I do not think it is important thw warehouse...
|
by: TC |
last post by:
I've used Access for many years. Several times, I've encountered a bug
which I refer to as the "Vanishing Joins" bug. When it happens, joins
vanish randomly from queries. More specifically, all...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |