wdarnellg
asked on
How Do I Show Both Parents of The Same Child
I have two tables, one with the names of adults and the other with the name of children. In the Children's table there are two columns, ParentID (Not Nullable) and OtherParentID (Nullable). I would like to query the tables so that when either of the parents IDs are presented as parameters, the other can show as the spouse or partner along with the related children from the Players table.
Is this possible with the tables I have? Or do I need to take a different approach?
Is this possible with the tables I have? Or do I need to take a different approach?
USE [DBName]
GO
/****** Object: Table [dbo].[ParentGuardian] Script Date: 03/09/2011 00:00:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ParentGuardian](
[ParentID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](20) NOT NULL,
[LastName] [nvarchar](20) NOT NULL,
[Gender] [nvarchar](6) NOT NULL,
[Birthdate] [date] NOT NULL,
[Ethnicity] [nvarchar](50) NOT NULL,
[Phone1] [nvarchar](14) NOT NULL,
[Phone2] [nvarchar](14) NULL,
[Phone3] [nvarchar](14) NULL,
[Address1] [nvarchar](50) NOT NULL,
[Address2] [nvarchar](50) NULL,
[City] [nvarchar](50) NOT NULL,
[State] [nvarchar](2) NULL,
[Zip] [nvarchar](11) NULL,
[Email] [nvarchar](175) NULL,
[Education] [nvarchar](50) NULL,
[Income] [nvarchar](50) NULL,
CONSTRAINT [PK_ParentGuardian] PRIMARY KEY CLUSTERED
(
[ParentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ParentGuardian] ADD CONSTRAINT [DF_ParentGuardian_State] DEFAULT (N'TX') FOR [State]
GO
USE [DBName]
GO
/****** Object: Table [dbo].[Players] Script Date: 03/09/2011 00:03:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Players](
[PlayerID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](20) NOT NULL,
[LastName] [nvarchar](20) NOT NULL,
[Gender] [nvarchar](6) NOT NULL,
[Birthdate] [date] NOT NULL,
[Ethnicity] [nvarchar](20) NOT NULL,
[ParentID] [int] NOT NULL,
[OtherParentID] [int] NULL,
CONSTRAINT [PK_Players] PRIMARY KEY CLUSTERED
(
[PlayerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Players] WITH CHECK ADD CONSTRAINT [FK_Players_ParentGuardian] FOREIGN KEY([ParentID])
REFERENCES [dbo].[ParentGuardian] ([ParentID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Players] CHECK CONSTRAINT [FK_Players_ParentGuardian]
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cool! That does work, even when I add the WHERE and OR for the parameters
ASKER