Re: Re: subtracting one query from the other

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 <dkokenge@netscape.net> To: php-db@lists.php.net 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