Lets see if anyone can help me out:
I have these 3 tables in a MySQL 3.22.27 Database and i'm running this query:
SELECT supplier.id,supplier.company_name,supplier.contact_name,supplier.address_street,
supplier.address_city,supplier.address_state,supplier.address_zip,supplier.phone_business,
supplier.url,supplier.miniweb_live,supplier.miniweb_name,supplier.special_live,subcatsicbond.
sub_catid,subcatsicbond.siccode
FROM subcatsicbond
LEFT JOIN suppliersiclink ON subcatsicbond.siccode=suppliersiclink.sic_code
LEFT JOIN supplier ON suppliersiclink.sup_id=supplier.id
WHERE ((subcatsicbond.sub_catid=37) AND (supplier.max_latitude<=32.99) AND
(supplier.min_latitude>=31.55) AND (supplier.min_longitude>=89.25) AND
(supplier.max_longitude<=90.97))
ORDER BY supplier.company_name ASC
LIMIT 0,10;
Which works fine, but takes forever, I've upgrade a test machine to
MySQL 3.23.33 and did a mysqldump on the 3.22.27 server of the database.
I imported the DB into MySQL 3.23.33 to run the query to test time
diffrences, however, I got unexpcted resutls.
In MySQL 3.22.27 when I run the above query, I get the proper results.
However, In MySQL 3.23.33 (using SAME database) I get NO results.
So, I changed the query around to this:
SELECT
supplier.id,supplier.company_name,supplier.contact_name,supplier.address_street,supplier.address_city,
supplier.address_state,supplier.address_zip,supplier.phone_business,supplier.url,supplier.miniweb_live,
supplier.miniweb_name,supplier.special_live,subcatsicbond.sub_catid,subcatsicbond.siccode
FROM supplier,subcatsicbond,suppliersiclink
WHERE ((suppliersiclink.sic_code=subcatsicbond.siccode) AND
(subcatsicbond.sub_catid=37)
AND (supplier.id=suppliersiclink.sup_id)
AND (supplier.max_latitude<=32.99) AND (supplier.min_latitude>=31.55)
AND (supplier.min_longitude>=89.25) AND (supplier.max_longitude<=90.97))
ORDER BY supplier.company_name ASC
LIMIT 0,10;
This query works fine in MySQL 3.22.27 (and faster too [by 2secs]) to get the same
results
as first query. However, it does not work in MySQL 3.23.33.
Here's what I'm trying to do (for those if you that feel you can help):
I have:
'supplier' table (~105,000 rows)
'subcatsicbond' table (~354 rows)
'suppliersiclink' table (~135,000 rows)
I need to JOIN all these tables, by:
1) pull list of siccodes from subcatsicbond
2) join those siccodes with table suppliersiclink to get supplier id
3) join suppler on suppliersiclink to get list of suppliers in that
sic area
Thanks for the help.
---------------------
Johnny Withers
[EMAIL PROTECTED]
p. 601.853.0211
c. 601.954.9133
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
