Post reply

Simple join

  • July 19, 2006 at 11:06 am

    #114801

    I am new to TSQL and I am wondering what is the best way to verify if data in a table is valid.  We have a table that contains a salesrep_code and I want to compare it to the salesrep_code table to be sure it is a valid code.  So I need to return any column in TableA.salesrep_code that does not have a matching salesrep_code in TableB.salesrep_code.  Can somebody help me figure out the best way to do this?  I tries using left outer join and got back tons of rows.

    Thanks so much!

    Isabelle

    Thanks!
    Bea Isabelle

  • July 19, 2006 at 11:12 am

    #650172

    Add: Where TableB.salesrep_code is NULL to the LEFT OUTER JOIN statement

    You should also investigate foreign keys so that you don't have to continue doing this.

  • July 19, 2006 at 11:25 am

    #650176

    Hi Pam,

    Yes, we are going to a new ERP system that will take care of the constraint issues and we are currently getting ready to migrate the data to the new system.  I added the 'where' and got 2 rows.  I just want to be sure I am doing this right and that this means all salesrep codes in my table are valid except for those two rows.  Here is what I have:

    OPCSHTO = table with salesrep_codes

    OPCSAMS = salesrep code table

    So my query looks like this:

    select o.cust_shipto_name, o.salesrep_code, s.salesrep_code

    from opcshto o left outer join opcsams s on

    (o.salesrep_code = s.salesrep_code)

    where s.salesrep_code is NULL

    Is this correct?  My thought process is to return any customer that has a salesrep_code in the OPCSHTO table that does not match (NULL) in the OPCSAMS table, correct? 

    Thanks!

    Isabelle

    Thanks!
    Bea Isabelle

  • July 19, 2006 at 12:44 pm

    #650199

    Your SELECT statement looks good to me.  It should return the results that you've described.  If it only returns 2 rows as you've said, you can always verify this to give yourself a warm-fuzzy feeling by doing a SELECT * FROM OPCSHTS WHERE salesrep_code IN (put your 2 codes here)

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • July 19, 2006 at 12:51 pm

    #650203

    Thanks.  I did not a spot check here and there and it seems to be working the way its supposed to.

    Isabelle

    Thanks!
    Bea Isabelle

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply