Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010
    Posts
    19

    Cool Unanswered: help in writing a query

    hi Friends ,

    please help me out in creating a query...

    Here is my condition...

    use colldb(datbase 1)

    select * from collection where collectionid(1,2,3,4,5,6,7,8)

    use cardb (database 2)
    select * from batch where batchid in(1,2,3,)



    Here the collectionid and batchid are same.

    The question is i want to get the ids 4,5,6,7,8 by comparing both the db and both the tables. please tell me how to write a query for this.

    I want to restrict the ids that is not available in batchid which is available in collectionid.

    please help me in this.

  2. #2
    Join Date
    Aug 2011
    Posts
    12

    Answer

    Hi ,

    You should write your query like below:

    select * from colldb(database 1)..collection
    where collectionid in(1,2,3,4,5,6,7,8)
    Except
    select * from cardb (database 2)..batch
    where batchid in(1,2,3,)

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just a few notes for madadi

    - EXCEPT doesn't work in all versions of sql server

    - the database names don't have parentheses

    - the number of columns in the two tables are likely not the same, which would cause an error
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2010
    Posts
    19
    thanks madadi..

    i will try your idea....

  5. #5
    Join Date
    Oct 2010
    Posts
    19

    Red face

    i tried still its not working.... can any help me out.

  6. #6
    Join Date
    Aug 2011
    Location
    Chennai, India
    Posts
    2
    Try this query.
    select C.collectionid from [colldb].[dbo].[collection] C where
    Not Exists(select 1 from [cardb].[dbo].[batch] B where A.CollectionID=B.BatchID)

    the namespace is used before the table name. [Database Name].[Schema].[Table] to query table from two different database.

Posting Permissions

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