Help with a JOIN

[bothwell]

I'm fairly new to JOINs, so I don't know what the syntax for this one should be.

 

What I want this to do is two things - I want it to delete any records from pages AND children where $pageid matches the other two columns, which works great. But I also want to delete anything from pages even if there's no corresponding record in children, which doesn't work.

 

DELETE FROM pages,children USING pages INNER JOIN children WHERE pages.id='$pageid' AND pages.id = children.childid

 

I've tried

 

DELETE FROM pages,children USING pages LEFT JOIN children WHERE pages.id='$pageid' AND pages.id = children.childid

 

and also

 

DELETE FROM pages,children USING pages JOIN children WHERE pages.id='$pageid' AND pages.id = children.childid OR pages.id ='$pageid'

 

but neither work, unfortunately. I'm not getting any errors, just no rows returned, so obviously my syntax is off. The MySQL documentation is quite opaque, so I'm hoping somebody here can help

[fenway]

Your syntax is a bit off... USING is wrong here (the column names aren't the same in both tables), and you need an ON clause.  Try this:

 

DELETE FROM pages,children LEFT JOIN children ON ( pages.id = children.childid )WHERE pages.id='$pageid'