how to write query in mysql 4.0

ChicoMendez

hi ,
the below is working on mysql 4.1 and above but i dont know how to wrtie it without the subquery

jos_dvd table has a listing of dvds $_COOKIE['location']
josblacklist is a list with the ids of the dvds blacklisted in certain countries
the users country code is stored in

SELECT count(*)
FROM jos_dvd f
WHERE f.published=1
AND f.id NOT IN (
SELECT b.sid FROM jos_blacklist b WHERE b.code = '".$_COOKIE['location']."')

any help appreciated

Chico

Piranha

First let me say that it is really stupid that you should have to make a workaround. Is there no possibility to get a database that can handle subqueries?

SELECT count(*)
FROM jos_dvd
LEFT JOIN jos_blacklist ON jos_dvd.id = jos_blacklist.sid AND jos_blaclist.code = $_COOKIE['something']
WHERE jos_dvd.published = 1
AND jos_blacklist.sid IS NULL

I think that the above should work, but don't know for sure. But don't use cookies without makeing it safe from database injection, it is easy to change a cookie and cause troubles.

MarkR

You should use MySQL 5 or above for new applications. Version 4 is ancient history.

The above can easily be done without a subquery using an outer join and IS NULL.

Mark

bradgrafelman

MarkR wrote:
You should use MySQL 5 or above for new applications.

And if your host can't provide for your needs, ditch them and find a competent one! This is definitely not a hard thing to do these days.