SQL query on 3 tables works, 5 tables doesn't work

[Pugman]

I'm new to complex SQL queries and I've been asked to create a reporting solution to an existing store. I started with 3 tables (myOrders, myOrderedItems and myProducts) to keep it simple. The code below works great for what I wanted to do:

<cfquery name="qryMakeReport" datasource="#dsn#">
SELECT
	myOrders.OrderNumber, myOrders.DateOfOrder, myOrderedItems.OrderNumber, myOrderedItems.CrtItemID, myOrderedItems.CrtQuantity, myProducts.ItemID, myProducts.ProductID, myProducts.ProductName, myProducts.InStock, myProducts.Category
FROM
	myOrders, myOrderedItems, myProducts
WHERE
	myOrders.OrderNumber = myOrderedItems.OrderNumber AND
	myOrderedItems.CrtItemID = myProducts.ItemID AND
	DateOfOrder BETWEEN #createODBCDate(startDate)# AND #createODBCDate(endDate)#
ORDER BY myOrders.DateOfOrder ASC
</cfquery>

The trouble started when I tried to add the archive tables (myOrdersArchived and myOrderedItemsArchived) into the mix. The table myOrdersArchived has the same exact structure as myOrders. The table myOrderedItemsArchived has the same structure as myOrderedItems. It seems that I need to UNION the Orders tables as well as the OrderedItems tables. I've tried UNIONS and JOINS and Subqueries without any luck. I usually get one of the following errors:

The specified field 'OrderNumber' could refer to more than one table listed in the FROM clause of your SQL statement.

The specified field 'DateOfOrder' could refer to more than one table listed in the FROM clause of your SQL statement.

Syntax error in FROM clause.

So, any help or a pointer in the right direction would be greatly appreciated.

 

[r937]

why are you adding the archived tables to the query?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Pugman]

Hi r937,

That's a good question. My best answer would be to provide maximum flexibility to the customer while keeping the "live" tables smaller and faster. The customer may want a Products Sold report for a 1 or 3 or 5 year period. I'm trying to cover any scenario.

I'm thinking now of copying the DateOfOrder to the Ordered Items tables. This way I can skip the 2 orders tables in my query which will allow even me to create a query that works.

I want to thank you again for your help on this same project back in April when you informed me that the database I was using broke the first final form rule of db design.

 

[r937]

i guess the reason i'm asking is because most times, when people create an "archive" table, it's because they didn't optimize their queries, which are using table scans to find the desired rows (instead of index searches), so they move most of the rows out of the live table to try to keep the performance from degrading too much

far better to use a single table that is properly optimized

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Pugman]

Sadly, I had to lookup "index searches" to see what you are talking about. I found a lot of discussion about which is quicker - table or index scans - with a lot of varying opinions. It seems to really depend on a lot of factors which I'm sure you are aware of.

Creating indexes will be something to look at in the future for me, but right now I'm willing to take any performance hit to just to get this thing working.

The store I'm working on only generates 15-20 orders a month. The product report will run once-a-week at most. So it seems to me that speed shouldn't be too much of a concern.

My latest attempt has resulted in 2 queries which I could then join with query of query (QOQ), but I don't like the fact that the Ordered Items query will return all items instead of only those within the date range:

<cfquery name="qryOrders" datasource="#dsn#">
	SELECT myOrdersArchived.OrderNumber, myOrdersArchived.DateOfOrder
	FROM myOrdersArchived
	UNION ALL
	SELECT myOrders.OrderNumber, myOrders.DateOfOrder
	FROM myOrders
	WHERE DateOfOrder BETWEEN #createODBCDate(startDate)# AND #createODBCDate(endDate)#
	ORDER BY DateOfOrder ASC
</cfquery>

<cfquery name="qryOrderedItems" datasource="#dsn#">
	SELECT myOrdersArchived.OrderNumber AS OrderID, myOrdersArchived.DateOfOrder AS OrderDate, myOrderedItemsArchived.OrderNumber AS OrderNo, myOrderedItemsArchived.CrtItemID, myOrderedItemsArchived.CrtQuantity, myProducts.ItemID, myProducts.ProductID, myProducts.ProductName, myProducts.InStock, myProducts.Category
	FROM myOrdersArchived, myOrderedItemsArchived
	INNER JOIN myProducts ON myOrderedItemsArchived.CrtItemID = myProducts.ItemID
	UNION ALL
	SELECT myOrders.OrderNumber AS OrderID, myOrders.DateOfOrder AS OrderDate, myOrderedItems.OrderNumber AS OrderNo, myOrderedItems.CrtItemID, myOrderedItems.CrtQuantity, myProducts.ItemID, myProducts.ProductID, myProducts.ProductName, myProducts.InStock, myProducts.Category
	FROM myOrders, myOrderedItems
	INNER JOIN myProducts ON myOrderedItems.CrtItemID = myProducts.ItemID
	ORDER BY OrderNo
</cfquery>

Can you help me make these 2 queries into 1 or should I copy the DateofOrder into the Ordered Items tables?

Thanks so much.

 

[r937]

okay, here's your original query --

SELECT myOrders.OrderNumber
     , myOrders.DateOfOrder
     , myOrderedItems.OrderNumber
     , myOrderedItems.CrtItemID
     , myOrderedItems.CrtQuantity
     , myProducts.ItemID
     , myProducts.ProductID
     , myProducts.ProductName
     , myProducts.InStock
     , myProducts.Category
  FROM myOrders
INNER
  JOIN myOrderedItems
    ON myOrderedItems.OrderNumber = myOrders.OrderNumber
INNER
  JOIN myProducts
    ON myProducts.ItemID = myOrderedItems.CrtItemID
 WHERE myOrders.DateOfOrder 
       BETWEEN #createODBCDate(startDate)# 
           AND #createODBCDate(endDate)#
ORDER 
    BY myOrders.DateOfOrder ASC

now, in order to incorporate your archive tables, we have to use UNIONs (as you suspected) -- combining the orders table with its archive, and the ordereditems table with its archive...

SELECT myOrders.OrderNumber
     , myOrders.DateOfOrder
     , myOrderedItems.OrderNumber
     , myOrderedItems.CrtItemID
     , myOrderedItems.CrtQuantity
     , myProducts.ItemID
     , myProducts.ProductID
     , myProducts.ProductName
     , myProducts.InStock
     , myProducts.Category
  FROM [red]( SELECT * FROM myOrders
         UNION ALL
         SELECT * FROM myOrdersArchived )[/red] AS myOrders
INNER
  JOIN [red]( SELECT * FROM myOrderedItems
         UNION ALL
         SELECT * FROM myOrderedItemsArchived )[/red] AS myOrderedItems
    ON myOrderedItems.OrderNumber = myOrders.OrderNumber
INNER
  JOIN myProducts
    ON myProducts.ItemID = myOrderedItems.CrtItemID
 WHERE myOrders.DateOfOrder 
       BETWEEN #createODBCDate(startDate)# 
           AND #createODBCDate(endDate)#
ORDER 
    BY myOrders.DateOfOrder ASC

see how that works?

unfortunately, it's not going to perform too well, but it will give you the right results

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Pugman]

I tried it and get this error:

Syntax error (missing operator) in query expression 'myOrderedItems.OrderNumber = myOrders.OrderNumber INNER JOIN myProducts ON myProducts.ItemID = myOrderedItems.CrtItemID'.

The error occurred in
23 : WHERE myOrders.DateOfOrder
24 : BETWEEN #createODBCDate(startDate)#
25 : AND #createODBCDate(endDate)#
26 : ORDER
27 : BY myOrders.DateOfOrder ASC

I'm using MS Access right now for testing if it matters. I'll be switching it to MS SQL Server once done.

Note: I've been getting the syntax error (missing operator) a lot myself in my recent attempts.

 

[r937]

I'm using MS Access right now for testing if it matters.

yeah, it matters :-(


ms access requires that you parenthesize your joins when there is more than one join

SELECT myOrders.OrderNumber     
     , myOrders.DateOfOrder     
     , myOrderedItems.OrderNumber     
     , myOrderedItems.CrtItemID     
     , myOrderedItems.CrtQuantity     
     , myProducts.ItemID     
     , myProducts.ProductID     
     , myProducts.ProductName     
     , myProducts.InStock     
     , myProducts.Category  
  FROM [red]([/red]
       ( SELECT * FROM myOrders
         UNION ALL
         SELECT * FROM myOrdersArchived ) AS myOrders
INNER
  JOIN ( SELECT * FROM myOrderedItems
         UNION ALL
         SELECT * FROM myOrderedItemsArchived ) AS myOrderedItems
    ON myOrderedItems.OrderNumber = myOrders.OrderNumber
       [red])[/red]
INNER
  JOIN myProducts
    ON myProducts.ItemID = myOrderedItems.CrtItemID
 WHERE myOrders.DateOfOrder
       BETWEEN #createODBCDate(startDate)#
           AND #createODBCDate(endDate)#
ORDER
    BY myOrders.DateOfOrder ASC

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Pugman]

That's it. Thank you so much. If I had known about the Access requirement or used SQL Server, I may have figured this out on my own. I tried a lot of subqueries in my attempts but, of course, didn't get anywhere because of Access.

Thanks again for everything including clueing me in to the index search.