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
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."')


any help appreciated

Chico

jmcc

ChicoMendez wrote:

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
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."')

You would have to rewrite it as a left join from what I remember.

Regards...jmcc

ChicoMendez

jmcc wrote:

You would have to rewrite it as a left join from what I remember.

Regards...jmcc

Hi

yes i thought that...
that would be the case if i wanted to get all dvds that were in the blacklist.. but whats putting me out is the NOT IN ... how do i integrate that into a left join ??

tnx

jmcc

ChicoMendez wrote:

Hi

yes i thought that...
that would be the case if i wanted to get all dvds that were in the blacklist.. but whats putting me out is the NOT IN ... how do i integrate that into a left join ??

tnx

Roughly (at 1044 Hrs I am still waiting for my brain to get in gear. ):

select tNew.something
from tNew left join tOld
on tNew.something=tOld.something where tOld.something is null;

This is the query to left join the new table tNew with old table tOld to detect new items/values tNew. Since the new items will not exist in the old table, the result of the join will be :

tOld - tNew
NULL - something
something - something
something - NULL *

The NULL value is where the item does not exist in the old table so it is a case of using IS NULL to find the results.

* This may be the case where the item exists in the old table but not in the new table. Though it might be better to rewrite the query swapping the tables.

Also the select (*) might not work with the left join since it could be ambiguous. You may have to specify what you want in the results.

Regards...jmcc

ChicoMendez

Thats great help
thanks alot!

chico