Post reply

how to join 2 tables with a composite key

  • July 25, 2012 at 7:30 am

    #261530

    Hi all,

    I have a table, InventoryDetail that has a composite key:

    warehouse_no

    Record_type

    TagLot_No

    None of these individually are unique, but with all 3 together they are. This is actually a COBOL application and we are moving the data into SQL Server in order to create reports.

    I am trying to connect this table to others and the only thing they have in common is the warehouse_no and since it's not unique this isn't working of course.

    Here is the query I tried, and failed with!

    Select *DATA*

    FROM InventoryDetail id

    LEFT JOIN SalesReporting sr

    ON id.Warehouse_No = sr.warehouse_no

    LEFT JOIN Category cat

    ON cat.Category_ID = sr.Category_ID

    LEFT JOIN Item i

    on i.Item_ID = sr.Item_ID

    There are 3 tables I need to join for this query and the Inventory Detail is the main issue, nothing really in common with the other tables they are requesting. And I can't change anything, this is an old system like I said in COBOL. I can modify the data or structure when I ETL it into SQL though if that would make a difference?

    If anyone has any suggestions I would really appreciate it, been working on this for awhile and my boss is waiting!

    thanks!

  • July 25, 2012 at 8:52 am

    #1517407

    Your question is not very clear. Kindly post DDL along with sample data.

    ~ Lokesh Vij

    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • July 25, 2012 at 9:10 am

    #1517422

    Record_Type & Tag_Lot_No must appear somewhere else in the database - hopefully in the tables you want to join to. If they do, you will need to add the tables with them into your FROM clause, then join the InventoryDetail table using joins from more than 1 table. If they don't, you can't join to them. Get some COBOL programs & try & find linking code, or ask someone who knows the old system if possible...

  • July 25, 2012 at 9:13 am

    #1517424

    +1

    Get in touch with some veteran from cobol team

    ~ Lokesh Vij

    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • July 25, 2012 at 9:28 am

    #1517435

    I agree as well, get with the team that supports the COBOL app and ask them to help you understand the tables used and how they relate to each other.

    I was in your position at one time, the difference was I was also part of the team the supported the COBOL application so I had intimate knowledge of the data and how each of the tables related to each other. made it much easier to extract and import into SQL Server for reporting purposes.

  • July 25, 2012 at 9:41 am

    #1517453

    thanks!

    talked to a programmer and found the other 2 fields in a table that I don't need, but will try to join in anyway and see if it works.

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

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