Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

need workaround for older version of MySQL 1

Status
Not open for further replies.

mrsbean

Technical User
Jul 14, 2004
203
US
I am stuck with a server which is using MySQL 3.23.58.

I have written some PHP/MySQL code which doesn't work on my web server, but works just fine on my local machine because my local machine has a later version of MySQL.

The SQL I need to revise is

Code:
SELECT tblmembership.*, areasofpractice.areas FROM areasofpractice INNER JOIN (memberap INNER JOIN tblmembership ON memberap.wsuaaID = tblmembership.wsuaaID) ON areasofpractice.areasID = memberap.apID where ListDir = 1 AND FirstName like '%' AND LastName like '%' AND (areasID = 4 OR areasID = 5 OR 1=1) ORDER BY LastName, FirstName ASC LIMIT 0,20

Can anyone tell me how to best re-write this query so that it will work on the older MySQL server?

MrsBean
 
Your MySQL version probably doesn't support the INNER JOIN ... ON syntax. This can be replaced by simply comma-joining the tables and moving the join conditions into the WHERE expression:
[tt]
SELECT tblmembership.*, areasofpractice.areas
FROM areasofpractice, memberap, tblmembership
where
areasofpractice.areasID = memberap.apID
AND memberap.wsuaaID = tblmembership.wsuaaID
AND ListDir = 1
AND FirstName like '%'
AND LastName like '%'
AND (areasID = 4 OR areasID = 5 OR 1=1)
ORDER BY LastName, FirstName ASC
LIMIT 0,20
[/tt]
 
Of course, the "...Name like '%'" bits are redundant and could possibly slow the query.
 
A simple and great answer that I can learn a lot from. Thank you.

MrsBean
 
INNER JOIN syntax was supported well before 3.23.58

i'm betting it was the unusual (invalid?) parenthesizing, and would like you to test this to see if it also works --
Code:
select tblmembership.*
     , areasofpractice.areas 
  from areasofpractice 
inner 
  join memberap 
    on memberap.apID = areasofpractice.areasID
inner 
  join tblmembership 
    on tblmembership.wsuaaID = memberap.wsuaaID
 where ListDir = 1 
order 
    by LastName
     , FirstName ASC LIMIT 0,20
(i left out the obviously redundant clauses)

r937.com | rudy.ca
 
Apparently, INNER JOIN ... ON was introduced in MySQL 3.23.17. However, it appears INNER JOIN without an ON or USING was available before that.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top