Date: 12/11/06 (MySQL Communtiy)    Keywords: database, web

[edit to add:] Problem solved - in the end I have merged the tables together so all the data is now in one table. Thank you everyone for your suggestions - it was just getting too complicated and dragging the search time down with the data split out.

Hi

I'm working on a project that has 600,000+ records and they have been divided up by state in the database (therefore there are 51 different tables - including DC) and I'm having a problem getting all of the data out of the tables when more than one table is selected.

My original select statement looks something like this:

-----

SELECT AZ_ID,AR_ID,AZ.BusinessName,AR.BusinessName,AZ.Address,AR.Address,AZ.City,AR.City,AZ.State,AR.State,
AZ.Zip,AR.Zip,AZ.Phone,AR.Phone,AZ.SalesVolume,AR.SalesVolume,AZ.ContactFirstLastTitle,AR.ContactFirstLastTitle
FROM AZ, AR WHERE AZ.SalesVolume = 'Less than $500,000' AND AZ.WebAddress IS NOT NULL
AND AR.SalesVolume = 'Less than $500,000' AND AR.WebAddress IS NOT NULL LIMIT 500

-----

I have tried doing joins but there are no common fields so it is returning the results of the last state selected.

Any assistance would be greatly appreciated as this has been stumping me for the past little while.

Source: http://community.livejournal.com/mysql/107000.html