Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2006
    Posts
    4

    Unanswered: a table/column to table/column data check (was "Help Please, SQL something Simple")

    Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.

    I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.

    Easy Peasy for you guys, any help would be appreciated.

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by shurl77
    Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.

    I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.

    Easy Peasy for you guys, any help would be appreciated.
    Welcome to the forum,ok here is your solution

    Code:
    CREATE TABLE t1(
                             col1  VARCHAR(20)
                            ,col2  VARCHAR(20)
                            ,col3  VARCHAR(20)
                            )
    
    CREATE TABLE t2(
                             col4 VARCHAR(20)
                            ,col5 VARCHAR(20)
                            )
    The sql statement to check content of a col1 is in col4 or not...
    Code:
    SELECT * FROM 
    t1,t2 
    WHERE t1.col1=t2.col4
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Sep 2006
    Posts
    4
    Many thanks, will give it a try a wee bit later and post result.

    Many thanks....

  4. #4
    Join Date
    Sep 2006
    Posts
    4
    Hi, this appears to work fine, however if I change the logic to check what is not in table2 it appears to go into a endless loop, any help appreciated.

  5. #5
    Join Date
    Sep 2006
    Posts
    4
    Hi, sorry after further testing, I may not have explained myself very well, apologies, I need to access row1 of table1 and then scan column2 in table2, the entire table, for the occurence of the data item in row1 table1.

    Many thanks in anticipation.

  6. #6
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by shurl77
    Hi, this appears to work fine, however if I change the logic to check what is not in table2 it appears to go into a endless loop, any help appreciated.
    Test it ...
    Code:
    insert into t1 values('joy','datta','harry')
    insert into t1 values('Pink','datta56','harry2')
    insert into t1 values('Sheep','datta5','harry3')
    insert into t1 values('Ghree','datta11','harry5')
    
    insert into t2 values('Sheep','datta5')
    insert into t2 values('Ghree','datta11')
    Code:
    SELECT * FROM 
    t1
    WHERE t1.col1 NOT IN(SELECT t2.col5 FROM t2)
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  7. #7
    Join Date
    Oct 2004
    Posts
    3
    Thanks sorted..

Posting Permissions

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