asked on

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

ASKER CERTIFIED SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

radcaesar

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 */

radcaesar

http://msdn.microsoft.com/en-us/library/ms177288.aspx

HainKurt

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

Alpesh Patel

select *
from HC_HeadCount_Main
Left Outer join HC_Leverage_Accounts on HC_Leverage_Accounts.HeadCountID = HC_HeadCount_Main.HeadCount_ID

fjalbert

ASKER

Thanks