• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 154
  • Last Modified:

SQL Join Table

Issue:
I am able to do this in Access, but have never don a Join in SQL,.
I want to create a One to Many Join Table.

I want to setup a User with many Accounts that relate to him.
I will give you the Tables that I am using:

Tables 1:
HC_HeadCount_Main:  This will hold the User’s Information

PK: HeadCount_ID


Tables 2
HC_Leverage_Accounts :    This table will hold the Many different Leverage Account  for the User in the HC_HeadCount_Main  table.

PK: Leverage_Accounts_ID
FK: HeadCountID   : Not Sure if i made the FK corrently


I want to make sure to setup the "Referential Integrity" so to make sure when i delete or update the Parent Row the Child Rows get updated as well.


Thanks Again

0
fjalbert
Asked:
fjalbert
1 Solution
 
Ephraim WangoyaCommented:

The joins should just be similar

select *
from HC_HeadCount_Main
inner join HC_Leverage_Accounts  on HC_Leverage_Accounts.HeadCountID   =  HC_HeadCount_Main.HeadCount_ID
0
 
radcaesarCommented:
CREATE TRIGGER tr_ParentTable_Update
    ON ParentTable
    FOR INSERT, UPDATE
AS
BEGIN
    IF UPDATE(IsDeleted) BEGIN
        DELETE FROM ct
            FROM inserted i
                INNER JOIN ChildTable ct
                    ON i.ID = ct.ParentID
                        AND ct.IsActive = 0
            WHERE i.IsDeleted = 1
    END /* IF */
END /* Trigger */
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
HainKurtSr. System AnalystCommented:
check this link

http://www.sqlservercentral.com/Forums/Topic635134-338-1.aspx#bm635137

see how the tables are created with relation (with cascade update/delete option)
CREATE TABLE Customer (
CustomerID int NOT NULL PRIMARY KEY IDENTITY(1,1),
FirstName nvarchar(50) NOT NULL,
SecondName nvarchar(50),
PhoneNumber nvarchar(50),
)

CREATE TABLE CreditCard (
CreditCardID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
CustomerID INT References Customer(CustomerID) ON DELETE CASCADE ON UPDATE CASCADE,
CardNumber NVARCHAR(50) NOT NULL,
)

then the join will be Left join:

select * 
  from Customer c left join CreditCard cc on c.CustomerID=cc.CustomerID

Open in new window

0
 
Alpesh PatelAssistant ConsultantCommented:
select *
from HC_HeadCount_Main
Left Outer join HC_Leverage_Accounts  on HC_Leverage_Accounts.HeadCountID   =  HC_HeadCount_Main.HeadCount_ID
0
 
fjalbertAuthor Commented:
Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now