query runs on 4.1 but not on 4.0

wikstov

When executing this query in one of my scripts on my local machine (MySQL 4.1.10), it runs without a problem.

SELECT lidID, familienaam, voornaam FROM lid WHERE straatID='37' AND huisnummer='12' AND lidID NOT IN (SELECT lidID FROM familielidmaatschap WHERE familieID='7')

on my webhost however (MySQL 4.0.23) it won't run (#1064 error)

is this due to a difference in the DB version?

and what can I do to solve it?

thanks a lot in advance

wikstov

LordShryku

http://dev.mysql.com/doc/mysql/en/rewriting-subqueries.html

wikstov

thanks a lot!

but euhm... I'm trying to rewrite this query, but whatever I try it won't work

any indications?

LordShryku

Well, I'm not a MySQL guy, so the syntax may be wrong here, but I'm thinking the left join is around what you'd want to use.

Something like this...

   SELECT a.lidID, 
          a.familienaam, 
          a.voornaam 
     FROM lid a
LEFT JOIN familielidmaatschap b
       ON a.lidID = b.lidID
    WHERE a.straatID = '37' 
      AND a.huisnummer = '12' 
      AND b.familieID != '7'

Though, looking at it, you may be able to just get away with a regular join.

   SELECT a.lidID, 
          a.familienaam, 
          a.voornaam 
     FROM lid a, familielidmaatschap b
    WHERE a.lidID = b.lidID
      AND a.straatID = '37'
      AND a.huisnummer = '12' 
      AND b.familieID != '7'

wikstov

I'm afraid that won't do it, because some of the records in 'lid' don't have an entry in 'familielidmaatschap'

LordShryku

That would be where the left join should work. It's taking the outer table (lid) and joining to the inner table (familielidmaatschap) based on what matches. Non matches should just be included outright.

shrug

It's along those lines. I'd look up the join syntax in the manual and play with it a bit