locked
How to make 2 relationships (one to many) between 2 tables RRS feed

  • Question

  • Hello everyone I have the following concerns of how to achieve two different relationships between two same tables, the problem is this. a table is record of shipments, the other staff that can send or receive, in the table of records of shipments, the two values should go, sender_id and reciver_id, but both values are the same staff table.
    Tuesday, October 16, 2018 10:17 AM

Answers

All replies

  • Hi,

    Do you mean two tables (Staff table and shipment table)?

    You can create two one to many relationships between the two tables with the primary key in the staff table and ywo foreign key in the shipment table, something like this :

    CREATE TABLE Staff
    (
    Staff_id int not null,
    CONSTRAINT PK_staff PRIMARY KEY NONCLUSTERED (Staff_id)
    )
    
    
    CREATE TABLE Shipment
    (
    sender_id int,
    receiver_id int,
    CONSTRAINT FK_sender_Shipment FOREIGN KEY (sender_id)     
        REFERENCES Staff (Staff_id)
    ,CONSTRAINT FK_receiver_Shipment FOREIGN KEY (receiver_id)     
        REFERENCES Staff (Staff_id)
    )


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered"Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]


    Tuesday, October 16, 2018 10:31 AM
  • Just create two columns sender_id and receiver_id and link both of them using foreign key references to staff_id column in the staffs table which will be its primary key

    so it will look like below example

    https://www.mssqltips.com/sqlservertip/3528/how-to-join-to-the-same-table-multiple-times-for-a-sql-server-query/

    in your case it will be like

    CREATE TABLE Staff
    (
    StaffID int PRIMARY KEY,
    StaffName,
    ...
    )
    
    
    CREATE TABLE Shipments
    (
    ShipmentID int PRIMARY KEY,
    ...
    sender_id int REFERENCES Staff(StaffID),
    received_id int REFERENCES Staff(StaffID),
    ...
    )


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page



    Tuesday, October 16, 2018 10:35 AM