How to relate 2 tables

  • Hi

    I have 2 tables. How 2 relate 2 tables. I want to relate No with DocumentNo. Order Lines can have multiple rowsOrder. No is Primary KeyNoDocDateCustomerOrder Lines. DocumentNo + ItemNo is Primary keyDocumentNoItemNoQuantity

    Thanks

     

     

  • What part are you having problems with?  You say you want to relate No with DocumentNo.  This feels trivial to do as long as those 2 columns ARE actually related.

    What I think would help is if you could:

    1 - post DDL and sample data so we can see your tables

    2 - post what you have tried that didn't work

    3 - post your results

    4 - post expected results

     

    With what you posted, it sound like you are just trying to do:

    SELECT *
    FROM [dbo].[Table1] AS T1
    JOIN [dbo].[Table2] AS T2 ON T1.No = T2.DocumentNo

    BUT in the above, I am making a LOT of assumptions.  I am assuming the JOIN type, I am assuming you want all columns, I am assuming you want all rows, I am assuming by "relate 2 tables" you mean you are wanting to SELECT the data, No and DocumentNo ARE actually relatable in their current form (ie don't need to CAST/CONVERT either of them or do any tweaks to them such as truncating any data prior to joining them), etc.

    You could very well be talking about making a foreign key to relate the data in which case the SELECT query I gave above makes no sense.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!

  • In other words, please read and heed the article at the first link in my signature line below. 😀  It'll really help us help you.

    Brian... You've been around for quite a while... please feel free to add it to you signature line if you would find it convenient.  Looking back at how long ago it was written, I should probably modernize it a bit although it still works just fine.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 3 posts - 1 through 3 (of 3 total)

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