Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2009
    Location
    Connecticut
    Posts
    15

    Unanswered: SQL query help...

    MS SQL Server 2000 SP4 -

    Ths query brings back 473 rows
    Code:
    use towerdb
    select * from tower.nbdocdeletedrecords where presentdate>= '04/07/2009' and presentdate<= '04/08/2009' order by towid
    This query brings back 474 rows (the same 473 as above and one extra)
    Code:
    use modify_title
    Select distinct towid,update_date,update_action from dbo.mat_audit_nbdoc where Update_Date >= '04/07/2009' and Update_Date < '04/08/2009'
    and update_action = 'DELETE'  order by towid
    I'd LIKE this query to show me only the one row that is different but I think my join might be wrong or I'm missing SOMETHING :


    Code:
    select a.towid,a.update_date,a.update_action,b.towid,b.presentdate
    
    from modify_title.dbo.mat_audit_nbdoc as a left outer join towerdb.tower.nbdocdeletedrecords as b 
    
    on (a.towid = b.towid)
    where 
                 a.Update_Date >= '04/07/2009'
    	and a.Update_Date < '04/08/2009'
    	
    	and b.presentdate>= '04/07/2009'
    	and b.presentdate<= '04/08/2009'
    order by a.towid
    I need it to only return the one row that is different.. can anyone help me out here?

  2. #2
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58
    and b.towid is null

  3. #3
    Join Date
    Apr 2009
    Location
    Connecticut
    Posts
    15
    Ran it like this (with and b.towid is NULL) and it returned zero rows : (

    Code:
    select a.towid,a.update_date,a.update_action,b.towid,b.presentdate
    from modify_title.dbo.mat_audit_nbdoc as a left outer join towerdb.tower.nbdocdeletedrecords as b 
    
    on (a.towid = b.towid)
    where 
            a.Update_Date >= '04/07/2009'
        and a.Update_Date < '04/08/2009'
        
        and b.presentdate>= '04/07/2009'
        and b.presentdate<= '04/08/2009'
        
        and b.towid is NULL
        
        
    order by a.towid

  4. #4
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58
    throw both results of each table into temp tables
    then do a select from table a where not in table b based on the Towid

    Select * from #tmpa where towid not in (select towid from #tmpb)

    #tmpa being the larger table

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    select t.towid, m.towid
    from (select towid 
            from towerdb.tower.nbdocdeletedrecords 
            where update_date >='04/07/2009'
               and update_date <= '04/08/2009') t full outer join
           (select towid
            from modify_title.dbo.mat_audit_nbdoc 
            where presentdate >= '04/07/2009'
               and presentdate <= '04/08/2009') m on t.towid = m.towid
    where t.towid is null
        or m.towid is null

  6. #6
    Join Date
    Apr 2009
    Location
    Connecticut
    Posts
    15
    Code:
    select t.towid, m.towid
    from (select towid 
            from  modify_title.dbo.mat_audit_nbdoc
            where Update_date >='04/07/2009'
               and Update_date <= '04/08/2009') t full outer join
           (select towid
            from towerdb.tower.nbdocdeletedrecords 
            where presentdate >= '04/07/2009'
               and presentdate <= '04/08/2009') m on t.towid = m.towid
    where t.towid is null
        or m.towid is null

    returns 143 rows : (

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •