Those of you who know me i’ve been working on a website lately for a brick company. http://www.beehivebrick.com to be exact. Things are going great with it, here is a bumb in the road that in encountered that i think might help out some other PHP and MySQL programmers.
If your like me, you dont keep up on the newest and best ways to do PHP or MySQL, so you probably dont know what versions are compatable with what. With MySQL i was doing something like this at home:
SELECT * FROM images WHERE imgID NOT IN (SELECT imageID FROM relation);
-The above worked great on my home box, with MySQL version 5.0.
On the clients webserver, Lunar Pages, it didnt work. So i started looking around at what i did wrong that might be a slight difference in different versions of MySQL, well it turns out that Lunar Pages is running MySQL 4.0.25, which in turn does not support subqueries. They didnt start supporting them until v4.1, which on my home machine i have 5.0.
Anyway, the nice thing about subquieries is that they are easier to read, and formulate. I didnt have to look up a tutorial to figure out the above SQL query, i just messed around for a little until it came out right. However, to convert that to a MySQL 4.0 or less standard, or even just convert it to a non-subquery you change it into this:
SELECT * FROM `images` LEFT JOIN relation on relation.imageID=imgID WHERE imageID IS NULL;
Wow, so thats kind of a mouth full compared to the subquery above. So in the end i guess that its safe to say, i like subqueries a lot more than i like joins. Of course that seem to be a regular occurance over here at my school anyway.
This is a reply