How can you get records that are in one table but not another?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: How can you get records that are in one table but not another?

  1. #1
    Ted Young Guest

    How can you get records that are in one table but not another?


    I have 2 tables with identical schema.

    Is there a way to query what records are in one table but not another?

    Any ideas or suggestions would be greatly appreciated.

    Thanks!

    Ted

  2. #2
    DaveSatz Guest

    Re: How can you get records that are in one table but not another?

    set nocount on
    create table #x
    ( id int
    , x varchar(30))
    go

    insert #x select 1, 'a'
    insert #x select 2, 'b'
    insert #x select 3, 'c'
    go

    create table #y
    ( id int
    , x varchar(30))
    go

    insert #y select 1, 'a'
    insert #y select 3, 'c'
    go

    SELECT *
    FROM #x x
    WHERE NOT EXISTS ( SELECT 1
    FROM #y y
    WHERE x.id = y.id )
    go

    drop table #x
    drop table #y
    go

    --
    HTH,
    David Satz
    SQL Server MVP
    Principal Software Engineer
    Hyperion Solutions
    { SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS }
    (Please reply to group only - emails answered rarely)
    -----------------------------------------------------------------
    "Ted Young" <tedyoung@connecticare.com> wrote in message
    news:3b84f6a1@news.devx.com...
    >
    > I have 2 tables with identical schema.
    >
    > Is there a way to query what records are in one table but not another?
    >
    > Any ideas or suggestions would be greatly appreciated.
    >
    > Thanks!
    >
    > Ted




  3. #3
    Ted Young Guest

    Re: How can you get records that are in one table but not another?


    I am doing this in ACCESS.

    Is there any way to determinte the records that differ using a JOIN operator?

    Thanks!

    Ted

    "DaveSatz" <davidNOSPAMsatz@yahoo.com> wrote:
    >set nocount on
    >create table #x
    >( id int
    >, x varchar(30))
    >go
    >
    >insert #x select 1, 'a'
    >insert #x select 2, 'b'
    >insert #x select 3, 'c'
    >go
    >
    >create table #y
    >( id int
    >, x varchar(30))
    >go
    >
    >insert #y select 1, 'a'
    >insert #y select 3, 'c'
    >go
    >
    >SELECT *
    >FROM #x x
    >WHERE NOT EXISTS ( SELECT 1
    > FROM #y y
    > WHERE x.id = y.id )
    >go
    >
    >drop table #x
    >drop table #y
    >go
    >
    >--
    >HTH,
    >David Satz
    >SQL Server MVP
    >Principal Software Engineer
    >Hyperion Solutions
    >{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS

    }
    >(Please reply to group only - emails answered rarely)
    >-----------------------------------------------------------------
    >"Ted Young" <tedyoung@connecticare.com> wrote in message
    >news:3b84f6a1@news.devx.com...
    >>
    >> I have 2 tables with identical schema.
    >>
    >> Is there a way to query what records are in one table but not another?
    >>
    >> Any ideas or suggestions would be greatly appreciated.
    >>
    >> Thanks!
    >>
    >> Ted

    >
    >



  4. #4
    DaveSatz Guest

    Re: How can you get records that are in one table but not another?

    Ted,

    in the prev. ex.:

    SELECT *
    FROM #x x
    LEFT OUTER JOIN #y y ON x.id = y.id

    would return NULL for columns in "y" of there was no row.

    Dave

    "Ted Young" <tedyoung@connecticare.com> wrote in message
    news:3b850a57$1@news.devx.com...
    >
    > I am doing this in ACCESS.
    >
    > Is there any way to determinte the records that differ using a JOIN

    operator?
    >
    > Thanks!
    >
    > Ted
    >
    > "DaveSatz" <davidNOSPAMsatz@yahoo.com> wrote:
    > >set nocount on
    > >create table #x
    > >( id int
    > >, x varchar(30))
    > >go
    > >
    > >insert #x select 1, 'a'
    > >insert #x select 2, 'b'
    > >insert #x select 3, 'c'
    > >go
    > >
    > >create table #y
    > >( id int
    > >, x varchar(30))
    > >go
    > >
    > >insert #y select 1, 'a'
    > >insert #y select 3, 'c'
    > >go
    > >
    > >SELECT *
    > >FROM #x x
    > >WHERE NOT EXISTS ( SELECT 1
    > > FROM #y y
    > > WHERE x.id = y.id )
    > >go
    > >
    > >drop table #x
    > >drop table #y
    > >go
    > >
    > >--
    > >HTH,
    > >David Satz
    > >SQL Server MVP
    > >Principal Software Engineer
    > >Hyperion Solutions
    > >{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } {

    VSS
    > }
    > >(Please reply to group only - emails answered rarely)
    > >-----------------------------------------------------------------
    > >"Ted Young" <tedyoung@connecticare.com> wrote in message
    > >news:3b84f6a1@news.devx.com...
    > >>
    > >> I have 2 tables with identical schema.
    > >>
    > >> Is there a way to query what records are in one table but not another?
    > >>
    > >> Any ideas or suggestions would be greatly appreciated.
    > >>
    > >> Thanks!
    > >>
    > >> Ted

    > >
    > >

    >




  5. #5
    Daniel Laurent Lemire Guest

    Re: How can you get records that are in one table but not another?


    "Ted Young" <tedyoung@connecticare.com> wrote:
    >
    >I have 2 tables with identical schema.
    >
    >Is there a way to query what records are in one table but not another?
    >
    >Any ideas or suggestions would be greatly appreciated.
    >
    >Thanks!
    >
    >Ted


    lets say to authors and authors2 table

    select auid, aulname, aufname from authors where
    auid not in (select auid from authors2)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center