asked on

Use Temporary Table multiple time in MySQL

I am having to convert a MS SQL Project to MySQL.
In MS SQL I have a stored Procedure with a complicated Query that uses a Temporary table multiple times to filter out Sub Query.
The Temporary table is filled at the Beginning of the Procedure with a variety of values based on Supplied Parameters.

The Question is how can I do this in MySQL which refuses to allow using a Temporary table twice in the same Query!

The Temporary table simply holds integers with are then used to filter in the pattern of
select * from table A where A.Field in (select Val from TempTable)
Inner Join (Select * from Table B where B.Field in (select Val from TempTable)) as SubTable where SubTable.Field = A.Field

ste5an

Post a concise and complete example. Cause it is possible to use a (temporary) table more than once in a single query.

NerdsOfTech

Are you really self-joining like this? If not, Please use differenciating example table names if not.

If so, try:

 SELECT s1.* FROM
 (
  SELECT A.* FROM 
  table A
  WHERE A.Field in (SELECT Val from TempTable)
 ) s1
 INNER JOIN 
 ( SELECT B.* FROM Table B 
   WHERE B.Field IN (SELECT Val from TempTable)
 ) s2
 ON s1.Field = s2.Field

Open in new window

Since the constraints are already present within IN, you might be able to do this:

 SELECT A.* FROM 
 table A
 INNER JOIN 
 table B 
 ON A.Field = B.Field
 WHERE A.Field IN (SELECT Val from TempTable) /*since A is already constrained to B by ON, another IN for B is not needed*/

Open in new window

Ron Kidd

ASKER

This is an extremely simplified version of the SP.

I am selecting ALL Products from the Product table and the sales from each week For ONLY SOME STORES.
I have been using it this way with MSSQL with a Temp Table holding the storeID's but it is giving errors in MySQL.

Script to Create Tables, Data and Procedure

--
-- Create table `store`
--
CREATE TABLE store (
  ID int(11) NOT NULL AUTO_INCREMENT,
  Name varchar(50) DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 4,
AVG_ROW_LENGTH = 5461,
CHARACTER SET latin1,
COLLATE latin1_swedish_ci;

--
-- Create table `sales`
--
CREATE TABLE sales (
  ID int(11) NOT NULL AUTO_INCREMENT,
  ProductID int(11) DEFAULT NULL,
  StoreID int(11) DEFAULT NULL,
  Quantity int(11) DEFAULT NULL,
  Date date DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 6,
AVG_ROW_LENGTH = 3276,
CHARACTER SET latin1,
COLLATE latin1_swedish_ci;

--
-- Create table `product`
--
CREATE TABLE product (
  ID int(11) NOT NULL AUTO_INCREMENT,
  Name varchar(50) DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 4,
AVG_ROW_LENGTH = 5461,
CHARACTER SET latin1,
COLLATE latin1_swedish_ci;

DELIMITER $$

--
-- Create procedure `test_proc`
--
CREATE DEFINER = 'root'@'localhost'
PROCEDURE test_proc ()
BEGIN


  DROP TABLE IF EXISTS v_StoreList;

  DROP TEMPORARY TABLE IF EXISTS v_StoreList;
  CREATE TEMPORARY TABLE v_StoreList (
    StoreID int
  );

  INSERT INTO v_StoreList (StoreID)
    VALUES (1);

  INSERT INTO v_StoreList (StoreID)
    VALUES (3);


  SELECT
    pr.ID,
    pr.Name,
    LastWeekSales.Quantity AS LastWeekSales,
    ThisWeekSales.Quantity AS ThisWeekSales
  FROM product pr
    LEFT OUTER JOIN (SELECT
        s.ProductID,
        SUM(s.Quantity) AS Quantity
      FROM sales s
        INNER JOIN store s1
          ON s.StoreID = s1.ID
      WHERE s.StoreID IN (SELECT
          *
        FROM v_StoreList vsl)
      AND s.Date < '2018-07-06'
      GROUP BY s.ProductID) AS LastWeekSales
      ON LastWeekSales.ProductID = pr.ID
    LEFT OUTER JOIN (SELECT
        s.ProductID,
        SUM(s.Quantity) AS Quantity
      FROM sales s
        INNER JOIN store s1
          ON s.StoreID = s1.ID
      WHERE s.StoreID IN (SELECT
          *
        FROM v_StoreList vsl)
      AND s.Date > '2018-07-05'
      GROUP BY s.ProductID) AS ThisWeekSales
      ON ThisWeekSales.ProductID = pr.ID;

END
$$

DELIMITER ;

-- 
-- Dumping data for table store
--
INSERT INTO store VALUES
(1, 'Store 1'),
(2, 'Store 2'),
(3, 'Store 3');

-- 
-- Dumping data for table sales
--
INSERT INTO sales VALUES
(1, 1, 1, 10, '2018-07-02'),
(2, 1, 1, 11, '2018-07-04'),
(3, 2, 1, 1, '2018-07-06'),
(4, 2, 3, 5, '2018-07-09'),
(5, 1, 5, 4, '2018-07-10');

-- 
-- Dumping data for table product
--
INSERT INTO product VALUES
(1, 'Product 1'),
(2, 'Product 2'),
(3, 'Product 3');

-- 
-- Restore previous SQL mode
-- 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

-- 
-- Enable foreign keys
-- 
/*!40014 SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS */;

Open in new window

NerdsOfTech

Use HAVING for WHERE conditions and convert the IN into INNER JOIN on the field you are filtering by in subqueries; try:

 SELECT
  pr.ID,
  pr.Name,
  LastWeekSales.Quantity AS LastWeekSales,
  ThisWeekSales.Quantity AS ThisWeekSales
 FROM product pr
 LEFT OUTER JOIN 
 (
  SELECT
   s.ProductID,
   SUM(s.Quantity) AS Quantity
  FROM sales s
  INNER JOIN store s1
   ON s.StoreID = s1.ID
  INNER JOIN v_StoreList vsl
   ON s.StoreID = vsl.StoreID
  GROUP BY s.ProductID
  HAVING s.Date < '2018-07-06'
 ) AS LastWeekSales
  ON LastWeekSales.ProductID = pr.ID
 LEFT OUTER JOIN 
 (
  SELECT
   s.ProductID,
   SUM(s.Quantity) AS Quantity
  FROM sales s
  INNER JOIN store s1
   ON s.StoreID = s1.ID
  INNER JOIN v_StoreList vsl
   ON s.StoreID = vsl.StoreID  
  GROUP BY s.ProductID
  HAVING s.Date > '2018-07-05'
  ) AS ThisWeekSales
   ON ThisWeekSales.ProductID = pr.ID;

Open in new window

Ron Kidd

ASKER

Thanks for your Reply.
Just a Question - How is that filtering by Store?
I can't see any WHERE or HAVING clause using the StoreID.

NerdsOfTech

The filter occurs on the INNER JOIN's that substituted the IN()'s. If and only if there's a matching store in the temp table, will it be returned. In both subqueries, this filter is being applied.

INNER JOIN v_StoreList vsl
   ON s.StoreID = vsl.StoreID

Open in new window

ste5an

Caveat: Using INNER JOIN to filter - instead of EXISTS(), not IN() - requries that the filter table is 1:1. Or in otherwords: The store id must be unique.

Ron Kidd

ASKER

Hello
Sorry for the Delay.

NerdsOrTech - I have copied you code into my MySQL Procedure and I get the Same Error. "Can't reopen table: 'vsl' " (Apologies for my previous question I didn't notice that the temp table was joined in the Sub Queries)

Only way I can get it to work is to Create Two temp tables and use each one twice.
Doesn't seem the Best way though - I would need to create 8 identical Temp Tables for the Production Script.

ste5an

Okay, my first post was non-sense. It is a problem to use a temporary table twice in the same query.

See B.5.6.2 TEMPORARY Table Problems

The most common solution to your problem is to use a CTE instead of a temporary table.

ASKER CERTIFIED SOLUTION

NerdsOfTech

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ron Kidd

ASKER

Gives a Different Error
According to this CTE are only available in MySQL 8.0 (I only have 5.6)

http://www.mysqltutorial.org/mysql-cte/

ste5an

Well, either you switch to the actual version or you need to use persistent temporary tables by using session_id() and procedure name as discriminator.

Ron Kidd

ASKER

To Clarify the Version - 5.6 will be the Production Version

NerdsOfTech

I am having to convert a MS SQL Project to MySQL.

Please use the latest version of MySQL.

Ron Kidd

ASKER

Appears there is No way for this to be done in MySQL 5.6.
I have had to resort to having the Temp Table duplicated 8 times in the Procedure.