sql

[sivak]

can anyone explain me one to one and one to many and many to one and many to many relationship in sql with example plz

[sknake]

Run this query. The comments are embedded.

IF OBJECT_ID('Men', 'U') IS NOT NULL DROP TABLE Men
Create Table Men
(
  Name varchar(30) PRIMARY KEY,
  Spouse varchar(30),
  DateOfBirth DateTime
)

IF OBJECT_ID('Women', 'U') IS NOT NULL DROP TABLE Women
Create Table Women
(
  Name varchar(30) PRIMARY KEY,
  Spouse varchar(30),
  DateOfBirth DateTime
)

IF OBJECT_ID('Children', 'U') IS NOT NULL DROP TABLE Children
Create Table Children
(
  Name varchar(30) PRIMARY KEY,
  Father varchar(30),
  Mother varchar(30),
  DateOfBirth DateTime
)

Insert Into Men (Name, Spouse, DateOfBirth) Values ('Scott', 'Nichole', GetDate()-10)
Insert Into Men (Name, Spouse, DateOfBirth) Values ('Sam', 'Jan', GetDate()-20)
Insert Into Men (Name, Spouse, DateOfBirth) Values ('Frank', 'Ashley', GetDate()-30)

Insert Into Women (Name, Spouse, DateOfBirth) Values ('Nichole', 'Scott', GetDate()-40)
Insert Into Women (Name, Spouse, DateOfBirth) Values ('Jan', 'Sam', GetDate()-50)
Insert Into Women (Name, Spouse, DateOfBirth) Values ('Ashley', 'Frank', GetDate()-60)

Insert Into Children (Name, Father, Mother, DateOfBirth) Values ('Kid1', 'Sam', 'Jan', Getdate()-70)
Insert Into Children (Name, Father, Mother, DateOfBirth) Values ('Kid2', 'Sam', 'Jan', Getdate()-80)
Insert Into Children (Name, Father, Mother, DateOfBirth) Values ('Kid3', 'Sam', 'Jan', Getdate()-90)
--Orpan record (no pun intended)
Insert Into Children (Name, Father, Mother, DateOfBirth) Values ('Kid4', 'No Dad', 'No Mom', Getdate()-90)
--No father in this case, but valid mother
Insert Into Children (Name, Father, Mother, DateOfBirth) Values ('Kid5', 'No Dad', 'Jan', Getdate()-90)

--Forget about divorices and unmarried people and we have a 1:1 relationship
Select *
From Men Inner Join Women On (Men.Name = Women.Spouse)

--We have a 1:many relationship with parents:children
Select *
From Men Inner Join Children On (Men.Name = Children.Father)

--We have a 1:many relationship with parents:children
Select *
From Women Inner Join Children On (Women.Name = Children.Mother)

--We have a 1:1:many relationship with husban:wife:children
--This shows kids by either parent but possibly a child born out of wedlock too
Select *
From Men Inner Join Women On (Men.Name = Women.Spouse)
         Inner Join Children On (Men.Name = Children.Father or Women.Name = Children.Mother)