Thanks for ur response. I used the approach as below and it worked much faster and reduced the execution time by 5 times. I concatenated the 3 fields i wanted to compare and used it as the id. But the query still takes like 3 minutes. Earlier it used to take 12 minutes. Can I optimise it any further. Please help. My query is as follows:
SELECT distinct a.playlist,a.material_id,a.destination_locator, a.destination,a.air_time,a.traffic_duration,a.comment,a.title, a.device_name,a.source_locator,a.source from record1 a left outer join record2 b on a.material_id+a.destination_locator+a.device_name=b.material_id+b.destination_locator+b.device_name where b.material_id+b.destination_locator+b.device_name is null help is greatly appreciated. regards, harpreet >From: Dan Koken <[EMAIL PROTECTED]> >To: [EMAIL PROTECTED] >Subject: [PHP-DB] Re: subtracting one query from the other >Date: Thu, 27 Jun 2002 05:24:41 -0700 > >Not sure what your data looks like or what exactly you are trying to do. >But what is wrong with the old standard compare for NULL. Something like >------------------ >SELECT >first_record.id as first_id, >second_record.id as second_id >FROM second_record >LEFT JOIN first_record >ON first_record.id = second_record.id >ORDER BY something >HAVING first_id = NULL > >Here only the second_records will be selected if there is no matching >first_record. > >HTH.. Good luck .. Dan >------------------------- > >Harpreet Kaur wrote: > >>Please help, I am stuck and my mind is frozen. >> >>I want to subtrack one query from the other. I have one query that >>brings all the records. The second gets another set of records. >>I want to subtract the second query from the first one . How do i do this? >> >>First query produces like 1000 records. Second has like 800 records i >>want to display the other 200 that dont exist in the first query. >> >>Please help. >> >>I have this but it is too slow and doesnt work in php but does in mssql. >>select distinct a.playlist, >> a.material_id, >> a.destination_locator, >> a.destination, >> a.air_time, >> a.traffic_duration, >> a.comment, >> a.title, >> a.device_name, >> m.media_locator as source_locator, >> m.medium_name as source >>from cch_dubber_dublist_view11 a left outer join lib_copy_view m >>on a.material_id=m.material_id where (A.material_id is not null) and >>(A.material_id <>'') >>and >> NOT EXISTS ( >> select null >> from >> dev_device_view d >> inner join >> lib_copy_view c >> on >> d.device_name= c.medium_name >> where >> c.material_id = a.material_id >> AND c.media_locator = a.destination_locator >> AND d.device_name = a.device_name >> ) >> >> >> >> >>_________________________________________________________________ >>Send and receive Hotmail on your mobile device: http://mobile.msn.com >> > > >-- >PHP Database Mailing List (http://www.php.net/) >To unsubscribe, visit: http://www.php.net/unsub.php _________________________________________________________________ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
