problems with JOIN statement.

[lifeson2112]

hello, I am having problems searching across multiple tables. I was told that I could do this by using a JOIN statement, but that doesn't seem to be working.

 

here's an example of one of my queries. It works fine when i test them each individually, but the join statements don't work. :

SELECT * FROM pegperego WHERE name regexp 'power' and name regexp 'wheels' and name regexp 'latch' or caption regexp 'power' and caption regexp 'wheels' and caption regexp 'latch' 
JOIN SELECT * FROM power_wheels WHERE name regexp 'power' and name regexp 'wheels' and name regexp 'latch' or caption regexp 'power' and caption regexp 'wheels' and caption regexp 'latch' 
JOIN SELECT * FROM power_wheels_wheels WHERE name regexp 'power' and name regexp 'wheels' and name regexp 'latch' or caption regexp 'power' and caption regexp 'wheels' and caption regexp 'latch' 
JOIN SELECT * FROM presto WHERE name regexp 'power' and name regexp 'wheels' and name regexp 'latch' or caption regexp 'power' and caption regexp 'wheels' and caption regexp 'latch' ;

 

these are all pretty much the same queries, but looking through different tables. My thought right now is that maybe the query is too big or I am using the join statement incorrectly. If anyone can tell me what I'm doing wrong it would be greatly appreciated.

[Wildbug]

Can I see the table definitions?

 

(Also see UNIONs, if that's what you're really doing.)

[fenway]

Well, you're JOINing without JOIN conditions, which like wildbug said, probably means you mean unions.

[lifeson2112]

Wildbug, the table definitions for all the tables are exactly the same. What I had in mind was to combine the results so that the columns lined up. If this isn't what the join statement does then I'm a little confused. I'm going to go ahead and look into the union keyword.

[Wildbug]

Okay, that's what I thought.  Look up UNION.  It combines the results of multiple queries and expects the same number of columns from each.

[bubblegum.anarchy]

Pretty much, replace the word JOIN with UNION.

[Wildbug]

Also, if you don't really need regular expressions, the following suggests that using LIKE would be, like, a bit faster.  If you're not familiar with it, it has simple wildcards, similar to some command line wildcards in many OSs -- "%" means any characters, "_" means one character.

 

mysql> SELECT BENCHMARK(1000000,'this is inside the thing' LIKE '%thin%');
+-------------------------------------------------------------+
| BENCHMARK(1000000,'this is inside the thing' LIKE '%thin%') |
+-------------------------------------------------------------+
|                                                           0 | 
+-------------------------------------------------------------+
1 row in set (0.26 sec)

mysql> SELECT BENCHMARK(1000000,'this is inside the thing' REGEXP 'thin');
+-------------------------------------------------------------+
| BENCHMARK(1000000,'this is inside the thing' REGEXP 'thin') |
+-------------------------------------------------------------+
|                                                           0 | 
+-------------------------------------------------------------+
1 row in set (3.03 sec)