0

I'm trying to get rows from an existing table (by columns: p_id, e_id, ts, data1, data2, data3) to a temporary table (indexed by p_id, e_id, ts), and then selecting specific rows from that temporary table as a result. Then terminate the temporary table (because that's what CREATE TEMPORARY does, right?)

I reviewed these 2 posts:

and got all confused with getting the query written properly (my query doesn't run). 

CREATE TEMPORARY TABLE IF NOT EXISTS 
  pet_temp ( INDEX(p_id, e_id, ts) )  
AS (
  SELECT p_id, e_id, ts, data1, data2, data3
  FROM processes_history
  WHERE e_id=4362 AND ts BETWEEN '2017-03-01' AND '2017-04-01'
) SELECT p_id, e_id, ts, data1, data2, data3 FROM pet_temp WHERE p_id IN (11,22,33,44,55,66,77,88,99);
Praveen Kumar Purushothaman
  • 164,888
  • 24
  • 203
  • 252
Imnotapotato
  • 5,308
  • 13
  • 80
  • 147

4 Answers4

3
CREATE TEMPORARY TABLE IF NOT EXISTS 
  pet_temp ( INDEX(p_id, e_id, ts) )  
AS (
  SELECT p_id, e_id, ts, data1, data2, data3
  FROM processes_history
  WHERE e_id=4362 AND ts BETWEEN '2017-03-01' AND '2017-04-01'
) ;
SELECT p_id, e_id, ts, data1, data2, data3 FROM pet_temp WHERE p_id IN (11,22,33,44,55,66,77,88,99);

Drop temporary table if exists pet_temp ;
Kedar Limaye
  • 1,041
  • 8
  • 15
  • This works! Is there a way to name the columns in "pet_temp"? Fetching the data as an associative array results NULLs. `while ( $row = db_fetch_assoc($query) ) { $new[$row['p_id']] = $row['e_id'];` – Imnotapotato May 14 '18 at 14:16
0
CREATE TEMPORARY TABLE IF NOT EXISTS 
  pet_temp ( INDEX(p_id, e_id, ts) )  
AS 
  SELECT p_id, e_id, ts, data1, data2, data3
  FROM processes_history
  WHERE e_id=4362 AND ts BETWEEN '2017-03-01' AND '2017-04-01';

 SELECT p_id, e_id, ts, data1, data2, data3 FROM pet_temp WHERE p_id IN (11,22,33,44,55,66,77,88,99);
DROP TEMPORARY TABLE IF EXISTS pet_temp;

Looks like you just need to remove the first set of (), and add a semicolon between the statements.

Mel
  • 36
  • 3
0

You can use below query, it will give desire in table:

CREATE TEMPORARY TABLE IF NOT EXISTS pet_temp (INDEX(p_id, e_id, ts))
SELECT p_id, e_id, ts, data1, data2, data3
FROM (
    SELECT p_id, e_id, ts, data1, data2, data3 
    FROM processes_history   
    WHERE e_id=4362 AND ts BETWEEN '2017-03-01' AND '2017-04-01'
) x
WHERE p_id IN (11,22,33,44,55,66,77,88,99);
SELECT * FROM pet_temp;

Don't need to drop pet_temp table, it will get dropped automatically one session is closed.

Vergiliy
  • 1,248
  • 1
  • 13
  • 22
Zero
  • 1
  • 2
-1

Why do you need the temporary table for if you're not planing on using it later?

The next won't do the job?

SELECT p_id, e_id, ts, data1, data2, data3 FROM processes_history WHERE e_id=4362 AND p_id IN (11,22,33,44,55,66,77,88,99) AND ts BETWEEN '2017-03-01' AND '2017-04-01';

Please correct me if i'm missing something

Mithrandir
  • 46
  • 5