SQL Query Quandry
Laphan
I know I've got to post my schema, etc, but I think its more of a logic
thing than table-specific.
If you can imagine how an Excel pivot table would display it, I want all of
my sales people listed down the left hand side of my report, all my stock
categories listed along the top of my report and then for each value that
these 'axis's cover I want the sum of my qty sold field.
Getting this on an ASP page is my problem, but if I can get the desired
result in an Excel pivot table then I will be happy to continue the rest.
My basic 4 data fields are SALESPERSON, STOCKCAT, QTYSOLD and DATESOLD (the
last one being because I want to offer the user the ability to enter a date
range and this is what it will come from.
I have created a query that shows me the exact data that I want, but only
where there is a QTYSOLD for a SALESPERSON and STOCKCAT.
My actual problem is that I want to show all of the SALESPERSONs and
STOCKCATs available and if some don't have a value for the above then they
show a value of 0.00.
I've really been trying since you last helped me and I think the reason why
the above isn't working is because I need to perform a different kind of
JOIN so that I can get all of the SALESPERSONs and STOCKCATs from the
relevant tables.
The only problem with this is that my tables are as follows and when I do a
JOIN in MS Query it says it can't be done on more than 2 tables:
tbl_SALESPERSONS contains SALESPERSONID and SALESPERSONNAME (I need all of
the SALESPERSONNAME)
which is linked to the SALESPERSONID in
tbl_INVOICEHEADER contains SALESPERSONID, INVOICEID
which is linked to the INVOICEID in
tbl_INVOICELINES contains INVOICEID, STOCKID, QTYSOLD, DATESOLD (I need
QTYSOLD and DATESOLD)
which is linked to the STOCKID in
tbl_STOCK contains STOCKID, STOCKCATID
which is linked to the STOCKCATID in
tbl_STOCKCAT contains STOCKCATID, STOCKCATNAME (I need all of the
STOCKCATNAME)
How can I do this when I need the above to link of it all together??
Your help (as always) would be appreciated.
Rgds
Laphan
onedaywhen
database question. Ideally this will be in the form of a SQL script
with CREATE TABLEs to, well, create the tables and INSERT INTOs to
create some sample data.
If you don't, you'll probably only get a theoretical answer along the
lines of, 'You need an OUTER JOIN specifically, and some GROUP BY
clauses generally'. If you had to ask the question, the chances are
you won't understand the answer without an example to work through.
It's not really fair to expect someone else to come up an example and
anyhow you'd be better off getting an answer relating to *your* tables
and data, rather than something involving employees, airplanes,
foobars and the like.
Also, being a simple fellow I find it quite hard to picture a four
table nested join in my head <g>, so the first thing I want to do is
re-create the tables to help with the visualisation.
Unless your DBMS has a tool for creating the DLL, it can be a pain to
do. SQL Server does but MS Access doesn't and, let's face it, that's
usually a show stopper in this ng (where the default DBMS is expected
to be Jet but almost universally referred to as 'Access' - can't see
the wood for the trees <g>). I'm working on a beta version of a tool
to churn out some DDL when pointed at an OLEDB data source, still a
bit ropey and user-unfriendly but available to anyone who's
interested, has VB6 and knows how to find me...
A happy medium is to pose your problem using data from an example
database most here will already have: I'm thinking northwind and pubs,
of course.
[BTW the same problem applies to Excel data, but there isn't even an
Excel equivalent of DDL. I once proposed that VBA code to recreate the
data should be posted. The only response, from the respected Jim Rech,
Excel MVP, said, 'I think I would be put off by a macro that dumped
the data; might take me longer to check the macro than type the data.'
Fair point I suppose. Perhaps someone with an Excel website in need
of some traffic could post up some standard worksheets as HTML and we
could say, 'Using the Customers worksheet at Chip's site to
demonstrate what I mean ...' Just a thought.]
Back on thread: I commend you for your effort to describe your schema,
most don't bother. I could *possibly* create some tables based on your
description, '<table> contains <columns> and is linked to <table>..,'
but it would take a long time, I'd have to make some (possibly
erroneous) assumptions (e.g. by 'linked' are your alluding to foreign
key constraints, linked servers or something else?), totally invent
data that both works and fails for your scenario and after all that
I'd feel like *I* was doing all the work. Plus I spent all my time on
that rant earlier...
I think you need to give a bit more. For example, you say, 'I have
created a query that shows me the exact data that I want, but only
where there is a QTYSOLD for a SALESPERSON and STOCKCAT.' I'd suggest
you at least post the SQL for this query. It's partially working so
it may simply need tweaking e.g. you've used a GROUP BY clause but no
HAVING clause etc.
--
"Laphan" <ne...@DoNotEmailMe.co.uk> wrote in message news:<#jM$f0yIEH...@TK2MSFTNGP10.phx.gbl>...
Dick Kusleika
> If you don't, you'll probably only get a theoretical answer along the
> lines of, 'You need an OUTER JOIN specifically, and some GROUP BY
> clauses generally'.
> > tbl_SALESPERSONS contains SALESPERSONID and SALESPERSONNAME (I need all
of
> > tbl_INVOICEHEADER contains SALESPERSONID, INVOICEID
> > tbl_INVOICELINES contains INVOICEID, STOCKID, QTYSOLD, DATESOLD (I need
> > tbl_STOCK contains STOCKID, STOCKCATID
> > tbl_STOCKCAT contains STOCKCATID, STOCKCATNAME (I need all of the
Along the theoretical lines, you just need to series a bunch of outer joins
using only two tables at a time, like
Query1: tbl_SALESPERSONS outer join tbl_INVOICEHEADER
Query2: tbl_INVOICELINES outer join tbl_STOCK
Query3: Query1 outer join Query2
Query4: tbl_STOCKCAT outer join Query3
Then use Query4 as the source of your pivot table. All this needs to be
done in SQL Server or Access or whatever you're using, not in MSQuery.
--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com
Laphan
Sorry for the spare info. It wasn't because I didn't want to display it. I
just didn't know how to get the data that you needed. I think I've got it
now so please note the following:
1) The DB is SQL 6.5.
2) My DDL for the tables in question:
/****** Object: Table [dbo].[SALESCENTRES] Script Date: 17/04/04
21:27:00 ******/
CREATE TABLE [dbo].[SALESCENTRES] (
[SALESCENTREID] [T_SALESCENTRESDOMAIN] NOT NULL ,
[NAME] [varchar] (40) NOT NULL ,
[COMMENT] [varchar] (255) NULL ,
[TIMESTAMP] [timestamp] NOT NULL
)
GO
/****** Object: Table [dbo].[SPOOLEDINVOICELINES] Script Date: 17/04/04
21:27:05 ******/
CREATE TABLE [dbo].[SPOOLEDINVOICELINES] (
[SPOOLEDINVOICEID] [T_SpooledInvoicesDomain] NOT NULL ,
[LINE] [int] NOT NULL ,
[STOCKID] [T_STOCKDOMAIN] NULL ,
[DESCRIPTION] [varchar] (255) NULL ,
[FREETEXTLINE] [tinyint] NULL ,
[UNITOFSALE] [varchar] (20) NULL ,
[QUANTITY] [float] NULL ,
[COSTPRICE] [float] NULL ,
[SELLINGPRICE] [float] NULL ,
[PRICELEVELID] [T_PRICELEVELSDOMAIN] NULL ,
[DISCOUNTPERCENT] [float] NULL ,
[NOMINALACCOUNTID] [T_NOMINALACCOUNTSDOMAIN] NULL ,
[VATRATEID] [T_VATRATESDOMAIN] NULL ,
[VATPERCENT] [float] NULL ,
[VATAMOUNT] [money] NULL ,
[LINEPRICE] [money] NULL ,
[TimeStamp] [timestamp] NULL
)
GO
/****** Object: Table [dbo].[SPOOLEDINVOICES] Script Date: 17/04/04
21:27:09 ******/
CREATE TABLE [dbo].[SPOOLEDINVOICES] (
[SPOOLEDINVOICEID] [T_SpooledInvoicesDomain] IDENTITY (1, 1) NOT NULL ,
[INVOICETYPE] [smallint] NOT NULL ,
[PEOPLEID] [T_PEOPLEDOMAIN] NOT NULL ,
[ACCOUNTID] [T_ACCOUNTSDOMAIN] NOT NULL ,
[ORDERNUMBER] [varchar] (40) NULL ,
[INVOICENUMBER] [int] NULL ,
[PREFIX] [varchar] (10) NULL ,
[SUFFIX] [varchar] (10) NULL ,
[INVOICEDATE] [datetime] NULL ,
[DATEDUE] [datetime] NULL ,
[PLACEDBY] [varchar] (40) NULL ,
[TELEPHONE] [varchar] (40) NULL ,
[LANGUAGEID] [T_LANGUAGESDOMAIN] NULL ,
[AGENTID] [T_AGENTSDOMAIN] NULL ,
[CUSTOMERTYPEID] [T_CUSTOMERTYPESDOMAIN] NULL ,
[SALESCENTREID] [T_SALESCENTRESDOMAIN] NULL ,
[DEPARTMENTID] [T_DEPARTMENTSDOMAIN] NULL ,
[WAREHOUSEID] [T_WAREHOUSESDOMAIN] NULL ,
[TRADINGTYPE] [smallint] NULL ,
[TRADINGDAYOFMONTH] [smallint] NULL ,
[TRADINGDAYSTIME] [smallint] NULL ,
[DISCOUNTPERCENT] [float] NULL ,
[SETTLEMENTPERCENT] [float] NULL ,
[PayNow] [tinyint] NULL ,
[AmountTendered] [money] NULL ,
[Balance] [money] NULL ,
[DiscountTaken] [money] NULL ,
[MethodOfPayment] [varchar] (40) NULL ,
[PayReference] [varchar] (40) NULL ,
[Authorisation] [varchar] (40) NULL ,
[PRICELEVELID] [T_PRICELEVELSDOMAIN] NULL ,
[TOTALNETT] [money] NULL ,
[TOTALVAT] [money] NULL ,
[TOTALDISCOUNT] [money] NULL ,
[TRADINGTERMS] [varchar] (40) NULL ,
[INVOICEMESSAGES] [varchar] (255) NULL ,
[COMMENT] [varchar] (255) NULL ,
[PLRate] [float] NULL ,
[BSRate] [float] NULL ,
[INVOICECONTACTID] [T_CONTACTDETAILSDOMAIN] NULL ,
[DELIVERYCONTACTID] [T_CONTACTDETAILSDOMAIN] NULL ,
[ORDERCONTACTID] [T_CONTACTDETAILSDOMAIN] NULL ,
[UserID] [T_UsersDomain] NULL ,
[TIMESTAMP] [timestamp] NOT NULL
)
GO
/****** Object: Table [dbo].[STOCK] Script Date: 17/04/04 21:27:13
******/
CREATE TABLE [dbo].[STOCK] (
[STOCKID] [T_STOCKDOMAIN] NOT NULL ,
[NAME] [varchar] (40) NOT NULL ,
[PICTURE] [varchar] (40) NULL ,
[WEIGHT] [float] NULL ,
[VOLUME] [float] NULL ,
[BARCODE] [smallint] NULL ,
[NumberOfPriceBreaks] [smallint] NOT NULL ,
[STOCKCATEGORYID] [T_STOCKCATEGORIESDOMAIN] NULL ,
[SALESNOMINALID] [T_NOMINALACCOUNTSDOMAIN] NULL ,
[PURCHASENOMINALID] [T_NOMINALACCOUNTSDOMAIN] NULL ,
[SELLINGCOMMENT] [varchar] (255) NULL ,
[INCLUDESELLINGCOMMENT] [tinyint] NULL ,
[DISPLAYSELLINGCOMMENT] [tinyint] NULL ,
[COSTCOMMENT] [varchar] (255) NULL ,
[DISPLAYCOSTCOMMENT] [tinyint] NULL ,
[PRODUCTTRACKING] [smallint] NULL ,
[ITEMTYPE] [smallint] NULL ,
[VALUATIONPRICE] [float] NOT NULL ,
[INCLUDEINCUSTOMERSTURNOVER] [tinyint] NULL ,
[INCLUDEINAGENTSTURNOVER] [tinyint] NULL ,
[SUPERCEDED] [tinyint] NULL ,
[SUPERCEDEDBY] [T_STOCKDOMAIN] NULL ,
[SUPPLIERID] [T_PEOPLEDOMAIN] NULL ,
[SUPPLIERSTOCKID] [varchar] (40) NULL ,
[SUPPLIERCOMMENT] [varchar] (255) NULL ,
[NEXTSERIALNUMBER] [int] NULL ,
[SERIALNUMBERLENGTH] [smallint] NULL ,
[SERIALNUMBERPREFIX] [varchar] (10) NULL ,
[SERIALNUMBERSUFFIX] [varchar] (10) NULL ,
[SERIALNUMBERPREFIXLENGTH] [smallint] NULL ,
[SERIALNUMBERSUFFIXLENGTH] [smallint] NULL ,
[TIMESTAMP] [timestamp] NOT NULL
)
GO
/****** Object: Table [dbo].[STOCKCATEGORIES] Script Date: 17/04/04
21:27:16 ******/
CREATE TABLE [dbo].[STOCKCATEGORIES] (
[STOCKCATEGORYID] [T_STOCKCATEGORIESDOMAIN] NOT NULL ,
[NAME] [varchar] (40) NOT NULL ,
[COMMENT] [varchar] (255) NULL ,
[TIMESTAMP] [timestamp] NOT NULL
)
GO
ALTER TABLE [dbo].[SALESCENTRES] WITH NOCHECK ADD
CONSTRAINT [pk_salescentres] PRIMARY KEY CLUSTERED
(
[SALESCENTREID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SPOOLEDINVOICELINES] WITH NOCHECK ADD
CONSTRAINT [pk_spooledinvoicelines] PRIMARY KEY CLUSTERED
(
[SPOOLEDINVOICEID],
[LINE]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SPOOLEDINVOICES] WITH NOCHECK ADD
CONSTRAINT [pk_spooledinvoices] PRIMARY KEY CLUSTERED
(
[SPOOLEDINVOICEID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[STOCK] WITH NOCHECK ADD
CONSTRAINT [pk_stock] PRIMARY KEY CLUSTERED
(
[STOCKID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[STOCKCATEGORIES] WITH NOCHECK ADD
CONSTRAINT [pk_stockcategories] PRIMARY KEY CLUSTERED
(
[STOCKCATEGORYID]
) ON [PRIMARY]
GO
I know there appears to be a lot of data here, but believe me I am only
wanting to use a small porttion of it.
3) My current query is as follows:
SELECT SALESCENTRES.NAME, STOCKCATEGORIES.NAME,
SPOOLEDINVOICELINES.QUANTITY, SPOOLEDINVOICES.INVOICEDATE
FROM SALESCENTRES, SPOOLEDINVOICELINES, SPOOLEDINVOICES, STOCK,
STOCKCATEGORIES
WHERE SPOOLEDINVOICELINES.SPOOLEDINVOICEID =
SPOOLEDINVOICES.SPOOLEDINVOICEID AND STOCK.STOCKID =
SPOOLEDINVOICELINES.STOCKID AND STOCKCATEGORIES.STOCKCATEGORYID =
STOCK.STOCKCATEGORYID AND SALESCENTRES.SALESCENTREID =
SPOOLEDINVOICES.SALESCENTREID AND ((SPOOLEDINVOICES.INVOICEDATE>{ts
'2004-04-01 00:00:00'}) AND (SPOOLEDINVOICES.INVOICEDATE<{ts '2004-08-01
00:00:00'}))
Where the 2 date values would actually be variable data that the user had
entered.
As I said before when I put the SALESCENTRES.NAME field in the left hand
portion of my Pivot table, the STOCKCATEGORIES.NAME in the top portion of my
Pivot table and the SPOOLEDINVOICELINES.QUANTITY in the data/middle section
of the pivot table I get the kind of result I want (see results below), but
it only retrieves data where I have values for the SALESCENTRES.NAME and the
STOCKCATEGORIES.NAME. What I want is to show all of the SALESCENTRES.NAME
fields (down the left) and all of the STOCKCATEGORIES.NAME fields (across
the top) and if there isn't a SPOOLEDINVOICELINES.QUANTITY value for a
particular cross section then it simply shows a zero. My examples below
should show it better.
My current pivot table shows:
StockCat1 StockCat4
SalesC1 25 3
SalesC2 4 6
Grand Total 29 9
But I want to show:
StockCat1 StockCat2 StockCat3
StockCat4 (etc...)
SalesC1 25 0 0
3
SalesC2 4 0 0
6
SalesC3 0 0 0
0
etc...
Grand Total 29 0 0
9
I'm sure it's down to joins, but I'm at a loss on how to do it.
I hope the above helps and appreciate any time you can give to it.
Rgds
Laphan
Dick Kusleika <di...@paragonUNMUNGEconstructioninc.com> wrote in message
news:eFc#Tx7IEH...@TK2MSFTNGP09.phx.gbl...
Laphan
I know I've talked about putting this in a pivot table, but is there anyway
of doing this kind of report without a pivot table?
All I want is the stock cats across the top, the sales centres down the left
and then if the cross-sectioning means that a qty was valid for that stock
cat against that sales centre then display it else show 0.00?
It looks like all the pivot table would do once my SQL was right would be to
sum the qtys that's it. Is that right??
Thanks
Laphan
Laphan <ne...@DoNotEmailMe.co.uk> wrote in message
news:40819...@127.0.0.1...
Laphan
onedaywhen
> Along the theoretical lines, you just need to series a bunch of outer joins
> using only two tables at a time, like
>
> Query1: tbl_SALESPERSONS outer join tbl_INVOICEHEADER
> Query2: tbl_INVOICELINES outer join tbl_STOCK
> Query3: Query1 outer join Query2
> Query4: tbl_STOCKCAT outer join Query3
>
> Then use Query4 as the source of your pivot table. All this needs to be
> done in SQL Server or Access or whatever you're using, not in MSQuery.
Dick, Why only two tables at a time in MS Query? Dosn't it just pass
the query to the DBMS? If the DBMS can handle multiple/nested outer
joins then surely so can MS Query. For example, for pubs on SQL
Server, this in the MS Query SQL window works for me:
SELECT DT1.au_lname, DT2.royaltyper
FROM
(
SELECT a.au_id, a.au_lname, t.royaltyper
FROM authors a LEFT JOIN titleauthor t
on a.au_id=t.au_id)
DT1 LEFT JOIN
(
SELECT a.au_id, a.au_lname, t.royaltyper
FROM authors a LEFT JOIN titleauthor t
on a.au_id=t.au_id
) DT2 ON DT1.au_id=DT2.au_id
--
Dick Kusleika
>
> Dick, Why only two tables at a time in MS Query? Dosn't it just pass
> the query to the DBMS? If the DBMS can handle multiple/nested outer
> joins then surely so can MS Query. For example, for pubs on SQL
> Server, this in the MS Query SQL window works for me:
>
> SELECT DT1.au_lname, DT2.royaltyper
> FROM
> (
> SELECT a.au_id, a.au_lname, t.royaltyper
> FROM authors a LEFT JOIN titleauthor t
> on a.au_id=t.au_id)
> DT1 LEFT JOIN
> (
> SELECT a.au_id, a.au_lname, t.royaltyper
> FROM authors a LEFT JOIN titleauthor t
> on a.au_id=t.au_id
> ) DT2 ON DT1.au_id=DT2.au_id
>
> --
I don't know that it is a limitation, I'm just going off what the OP said.
I do know that it's a limitation in Access, at least in the UI, that it
doesn't parse two outer joins because it calls them ambiguous. I don't even
think that you can override that in SQL view, but I'm not sure about that.
I had assumed that the OP already tried what you show above and got an
error. Maybe he tried it in the UI and not in SQL view.
onedaywhen
when I'm at my SQL Server machine. If you see this first, how about
some sample data too?
--
"Laphan" <ne...@DoNotEmailMe.co.uk> wrote in message news:<ekPR2dTJ...@TK2MSFTNGP09.phx.gbl>...
> Hi Dick/OneDay
>
> Sorry for the spare info. It wasn't because I didn't want to display it. I
> just didn't know how to get the data that you needed. I think I've got it
> now so please note the following:
>
> 1) The DB is SQL 6.5.
>
> 2) My DDL for the tables in question: <snip>
onedaywhen
Query. Thanks for clarifying.
BTW I think the support for nested outer joins and derived tables in
SQL was introduced in Jet 4.0 and SQL Server 6.5 respectively i.e.
both these queries work for me in MS Query against the northwind
database (the one shipped with VB6, Access97 I think) and can't see
why they wouldn't work in the SQL view of a query within MS Access
(but I can't test because I don't have MS Access!!):
Query1 (outer join on derived tables):
SELECT DT1.CompanyName, DT2.ShippedDate
FROM
(
SELECT cs.CustomerID, cs.CompanyName, od.ShippedDate
FROM Customers cs LEFT JOIN Orders od
ON cs.CustomerID=od.CustomerID
) DT1 LEFT JOIN
(
SELECT cs.CustomerID, cs.CompanyName, od.ShippedDate
FROM Customers cs LEFT JOIN Orders od
ON cs.CustomerID=od.CustomerID
) DT2 ON DT1.CustomerID=DT2.CustomerID;
Query2 (nested outer joins):
SELECT cs1.CustomerID, cs1.CompanyName, od.ShippedDate
FROM
(
Customers cs1 LEFT JOIN Orders od1
ON cs1.CustomerID=od1.CustomerID
) LEFT JOIN Orders od
ON od1.CustomerID=od.CustomerID;
--
"Dick Kusleika" <di...@paragonUNMUNGEconstructioninc.com> wrote in message news:<uvIygcj...@TK2MSFTNGP11.phx.gbl>...
> ODW
onedaywhen
far as creating your tables locally, putting in some test data and
re-writing your query (retaining all INNER JOINs) in SQL-92 join
syntax. Two reasons for this: 1) it removes the join criteria from the
WHERE clause, making it easier to read; 2) nested *outer* joins are
only supported for SQL-92 syntax. Watch this space (you should also
consider posting in one of the SQL Server ngs). FWIW, here's the
re-write with nested INNER JOINs:
SELECT
SALESCENTRES.NAME,
STOCKCATEGORIES.NAME,
SPOOLEDINVOICELINES.QUANTITY,
SPOOLEDINVOICES.INVOICEDATE FROM
(
(
(
SALESCENTRES INNER JOIN SPOOLEDINVOICES
ON SALESCENTRES.SALESCENTREID =
SPOOLEDINVOICES.SALESCENTREID
)
INNER JOIN SPOOLEDINVOICELINES
ON SPOOLEDINVOICELINES.SPOOLEDINVOICEID =
SPOOLEDINVOICES.SPOOLEDINVOICEID
)
INNER JOIN STOCK
ON STOCK.STOCKID =
SPOOLEDINVOICELINES.STOCKID
)
INNER JOIN STOCKCATEGORIES
ON STOCKCATEGORIES.STOCKCATEGORYID =
STOCK.STOCKCATEGORYID
WHERE
SPOOLEDINVOICES.INVOICEDATE
> '01 APR 2004 00:00:00'
AND SPOOLEDINVOICES.INVOICEDATE
< '01 AUG 2004 00:00:00'
;
--
oneda...@fmail.co.uk (onedaywhen) wrote in message news:<b8c9d0b7.04041...@posting.google.com>...
onedaywhen
You've gone quiet on me. In absence of some meaningful data from you,
this is the best I can do (tested in the MS Query SQL window):
SELECT
SALESCENTRES.NAME,
STOCKCATEGORIES.NAME,
SPOOLEDINVOICELINES.QUANTITY,
SPOOLEDINVOICES.INVOICEDATE FROM
(
(
(
SALESCENTRES LEFT JOIN SPOOLEDINVOICES
ON SALESCENTRES.SALESCENTREID =
SPOOLEDINVOICES.SALESCENTREID
)
LEFT JOIN SPOOLEDINVOICELINES
ON SPOOLEDINVOICELINES.SPOOLEDINVOICEID =
SPOOLEDINVOICES.SPOOLEDINVOICEID
)
LEFT JOIN STOCK
ON STOCK.STOCKID =
SPOOLEDINVOICELINES.STOCKID
)
FULL OUTER JOIN STOCKCATEGORIES