multiple queries in multi tables MYSQL

sgtpepper

I am trying to for a query that would be similar to the following

select * from residential AND multi WHERE town='Claremont NH'

Basically, the idea is for the query to search the TOWN field in both residential and multi tables and spit back the results. Most of the fields in the two tables are similar....but they do have different fields between the tables.

I was told that MSSQL would use a UNION but this is not the case with mySQL.

How can I do the above query?

stolzyboy

it should be

select * from residential, multi WHERE town='Claremont NH'

sgtpepper

When I try that, I get an error that says

ERROR 1052: Column: 'town' in where clause is ambiguous

stolzyboy

try this

select residential., multi. from residential, multi WHERE residential.town='Claremont NH' or multi.town='Claremont NH'

sgtpepper

hmmm....

It seems that mysql has accepted the query (no errors) but it is taking FOREVER!

Still no results after well over 2 minutes of processing. The results should only be about 40-50 records.

It's just hangin' there 🙂

stolzyboy

do you need to select every field from the tables, so what i mean is do you need to select * or don't you need all the fields right away

sgtpepper

Not really...but I am in the bad habit of using * during testing.

I just tried this and got a different error

mysql> select Id from residential, multi WHERE residential.town='Claremont NH' or multi.town='Claremont NH';
ERROR 1052: Column: 'Id' in field list is ambiguous

stolzyboy

select residential.Id, multi.Id from residential, multi WHERE residential.town='Claremont NH' or multi.town='Claremont NH';

sgtpepper

This produced
468987 rows in set (26.61 sec)

Only 50 or so records really match the query criteria though....

stolzyboy

try these three

select residential.Id, multi.Id from residential, multi WHERE residential.town='Claremont NH' || multi.town='Claremont NH';

select residential.Id, multi.Id from residential, multi WHERE residential.town='Claremont NH' and multi.town='Claremont NH';

select residential.Id, multi.Id from residential, multi WHERE residential.town='Claremont NH' && multi.town='Claremont NH';

sgtpepper

Intersting

Here are my results from the last query example you gave

| 111138 | 892690 |
| 899425 | 892690 |
| 112310 | 892690 |
| 107761 | 892690 |
| 105620 | 892690 |
| 102853 | 892690 |
| 110120 | 892690 |
| 102738 | 892690 |
| 102948 | 892690 |
| 104431 | 892690 |
| 101696 | 892690 |
| 100829 | 892690 |
| 100673 | 892690 |
| 100938 | 892690 |
| 100455 | 892690 |
| 872366 | 892690 |
| 113715 | 892690 |
| 894614 | 892690 |
| 103533 | 892690 |
+--------+--------+

It seems that the results for multi are repeating.

stolzyboy

hmmm... don't know, you're just gonna have to work out your wheres and such, go to www.mysql.com and do a search on join, that will give you some more help