MySql: Problem when using a temporary table

Question

Hi, I'm trying to use a temporary tables to store some values I need for a query. The reason of using a temporary table is that I don't want to store the data permanently so different users can modify it at the same time. That data is just stored for a second, so I think a temporary table is the best approach for this.

The thing is that it seems that the way I'm trying to use it is not right (the query works if I use a permanent one).

This is an example of query:

CREATE TEMPORARY TABLE SearchMatches (PatternID int not null primary key, Matches int not null) 
INSERT INTO SearchMatches (PatternID, Matches) 
VALUES ('12605','1'),('12503','1'),('12587','2'),('12456','1'),
('12457','2'),('12486','2'),('12704','1'),(' 12686','1'),
('12531','2'),('12549','1'),('12604','1'),('12504','1'),
('12586','1'),('12548','1'),('12 530','1'),('12687','2'),
('12485','1'),('12705','1') 

SELECT pat.id, signatures.signature, products.product, versions.version, builds.build, pat.log_file, sig_types.sig_type, pat.notes, pat.kb 
FROM patterns AS pat 
INNER JOIN signatures ON pat.signature = signatures.id 
INNER JOIN products ON pat.product = products.id 
INNER JOIN versions ON pat.version = versions.id 
INNER JOIN builds ON pat.build = builds.id 
INNER JOIN sig_types ON pat.sig_type = sig_types.id, SearchMatches AS sm 
INNER JOIN patterns ON patterns.id = sm.PatternID 
WHERE sm.Matches <> 0 
ORDER BY sm.Matches DESC, products.product, versions.version, builds.build 
LIMIT 0 , 50

Any suggestion?

Thanks.

Answer 1

I´m guessing but i think the query should be:

SELECT pat.id, signatures.signature, products.product, versions.version, builds.build, pat.log_file, sig_types.sig_type, pat.notes, pat.kb 
FROM patterns AS pat 
INNER JOIN signatures ON pat.signature = signatures.id 
INNER JOIN products ON pat.product = products.id 
INNER JOIN versions ON pat.version = versions.id 
INNER JOIN builds ON pat.build = builds.id 
INNER JOIN sig_types ON pat.sig_type = sig_types.id  
INNER JOIN SearchMatches AS sm ON pat.id = sm.PatternID 
WHERE sm.Matches <> 0 
ORDER BY sm.Matches DESC, products.product, versions.version, builds.build 
LIMIT 0 , 50;

these two lines dont seems right to me:

INNER JOIN sig_types ON pat.sig_type = sig_types.id, SearchMatches AS sm 
INNER JOIN patterns ON patterns.id = sm.PatternID

And if you need the two joins with patterns table, i think you should avoid mixing comma-separated with explicit JOIN clauses.

Answer 2

Thanks for the replies. This is the solution I finally got:

CREATE TEMPORARY TABLE SearchMatches ( PatternID int not null primary key, Matches int not null);
INSERT INTO SearchMatches 
 ( PatternID, Matches ) VALUES ( 12605, 1 )
 , ( 12503, 1 )
 , ( 12587, 2 )
 , ( 12456, 1 )
 , ( 12457, 2 )
 , ( 12486, 2 )
 , ( 12704, 1 )
 , ( 12686, 1 )
 , ( 12531, 2 )
 , ( 12549, 1 )
 , ( 12604, 1 )
 , ( 12504, 1 )
 , ( 12586, 1 )
 , ( 12548, 1 )
 , ( 12530, 1 )
 , ( 12687, 2 )
 , ( 12485, 1 )
 , ( 12705, 1 ) ;

SELECT pat.id
 , signatures.signature
 , products.product
 , versions.version
 , builds.build
 , pat.log_file
 , sig_types.sig_type
 , pat.notes
 , pat.kb  FROM SearchMatches AS sm INNER JOIN patterns AS pat
ON pat.id = sm.PatternID INNER JOIN signatures
ON signatures.id = pat.signature INNER JOIN products
ON products.id = pat.product  INNER JOIN versions
ON versions.id = pat.version INNER JOIN builds
ON builds.id = pat.build INNER JOIN sig_types
ON sig_types.id = pat.sig_type WHERE sm.Matches <> 0 ORDER BY sm.Matches DESC
 , products.product
 , versions.version
 , builds.build LIMIT 0 , 50;