Creating relationship with Parent/Child table
-
Please assist in creating the appropriate relationship between tables: PI_ServiceRequest and PI_ServiceRequestAssociatedFBO.
On what columns do i join ?
-
Oh, you've GOT to be kidding. I'm not downloading that. Post your CREATE TABLE statements, and maybe a handful of INSERTs for each table to show the relationship. Foreign keys aren't rocket science.
-
The two columns that match on both tables contains duplicate values, so i cant join on that as a primary key-secondary key relationship. That is the issue.
USE [PPECB]
GO
/****** Object: Table [dbo].[PI_ServiceRequestAssociatedFBO$] Script Date: 2022/02/10 12:45:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PI_ServiceRequestAssociatedFBO$](
[id] [float] NULL,
[fbocode] [nvarchar](255) NULL,
[servicerequestid] [float] NULL,
[status] [float] NULL,
[nvarchar](255) NULL,[DocumentId] [float] NULL,
[IsApproved] [float] NULL
) ON [PRIMARY]
GO
AND
USE [PPECB]
GO
/****** Object: Table [dbo].[ServiceRequest_3] Script Date: 2022/02/10 12:46:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ServiceRequest_3](
[id] [int] NOT NULL,
[activitypoint] [float] NULL,
[startdate] [datetime] NULL,
[enddate] [datetime] NULL,
[fbocode] [nvarchar](255) NULL,
[ordertype] [float] NULL,
[ordercategory] [float] NULL,
[requestorid] [nvarchar](255) NULL,
[quotation] [float] NULL,
[address] [nvarchar](255) NULL,
[inspectiontype] [float] NULL,
[analysistype] [float] NULL,
[certificationtype] [float] NULL,
[volume] [float] NULL,
[targetregion] [float] NULL,
[targetcountry] [float] NULL,
[servicerequeststate_id] [int] NOT NULL,
[submittedforapproval] [float] NULL,
[IsApproved] [float] NULL,
[datecreated] [datetime] NULL,
[datemodified] [datetime] NULL,
[dateapproved] [datetime] NULL,
[modifiedby] [float] NULL,
[approvedby] [float] NULL,
[acceptedterms] [float] NULL,
[structurecode] [int] NOT NULL,
[rejectionreason] [nvarchar](255) NULL,
[iscancelled] [float] NULL,
CONSTRAINT [PK_ServiceRequest_] PRIMARY KEY CLUSTERED
(
[id] 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].[ServiceRequest_3] WITH CHECK ADD CONSTRAINT [FK_ServiceRequest_3_Status_2] FOREIGN KEY([servicerequeststate_id])
REFERENCES [dbo].[Status_2] ([servicerequeststate_id])
GO
ALTER TABLE [dbo].[ServiceRequest_3] CHECK CONSTRAINT [FK_ServiceRequest_3_Status_2]
GO
ALTER TABLE [dbo].[ServiceRequest_3] WITH CHECK ADD CONSTRAINT [FK_ServiceRequest_3_Structure_] FOREIGN KEY([structurecode])
REFERENCES [dbo].[Structure_] ([structurecode])
GO
ALTER TABLE [dbo].[ServiceRequest_3] CHECK CONSTRAINT [FK_ServiceRequest_3_Structure_]
GO
-
Thank you for including DDL, but please use the insert/edit code sample so that your code is readable.
Not knowing the data, just guessing based on names & primary key, it looks like PI_ServiceRequestAssociatedFBO$ would relate to ServiceRequest_3 on [PI_ServiceRequestAssociatedFBO$].servicerequestid= ServiceRequest_3.ID... although both also have an fbocode column.
USE [PPECB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PI_ServiceRequestAssociatedFBO$](
[id] [float] NULL,
[fbocode] [nvarchar](255) NULL,
[servicerequestid] [float] NULL,
[status] [float] NULL,
[nvarchar](255) NULL,
[DocumentId] [float] NULL,
[IsApproved] [float] NULL
) ON [PRIMARY]
GO
/*
AND <--This won't execute
*/
USE [PPECB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ServiceRequest_3](
[id] [int] NOT NULL,
[activitypoint] [float] NULL,
[startdate] [datetime] NULL,
[enddate] [datetime] NULL,
[fbocode] [nvarchar](255) NULL,
[ordertype] [float] NULL,
[ordercategory] [float] NULL,
[requestorid] [nvarchar](255) NULL,
[quotation] [float] NULL,
[address] [nvarchar](255) NULL,
[inspectiontype] [float] NULL,
[analysistype] [float] NULL,
[certificationtype] [float] NULL,
[volume] [float] NULL,
[targetregion] [float] NULL,
[targetcountry] [float] NULL,
[servicerequeststate_id] [int] NOT NULL,
[submittedforapproval] [float] NULL,
[IsApproved] [float] NULL,
[datecreated] [datetime] NULL,
[datemodified] [datetime] NULL,
[dateapproved] [datetime] NULL,
[modifiedby] [float] NULL,
[approvedby] [float] NULL,
[acceptedterms] [float] NULL,
[structurecode] [int] NOT NULL,
[rejectionreason] [nvarchar](255) NULL,
[iscancelled] [float] NULL,
CONSTRAINT [PK_ServiceRequest_] PRIMARY KEY CLUSTERED
(
[id] 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].[ServiceRequest_3] WITH CHECK ADD CONSTRAINT [FK_ServiceRequest_3_Status_2] FOREIGN KEY([servicerequeststate_id])
REFERENCES [dbo].[Status_2] ([servicerequeststate_id])
GO
ALTER TABLE [dbo].[ServiceRequest_3] CHECK CONSTRAINT [FK_ServiceRequest_3_Status_2]
GO
ALTER TABLE [dbo].[ServiceRequest_3] WITH CHECK ADD CONSTRAINT [FK_ServiceRequest_3_Structure_] FOREIGN KEY([structurecode])
REFERENCES [dbo].[Structure_] ([structurecode])
GO
ALTER TABLE [dbo].[ServiceRequest_3] CHECK CONSTRAINT [FK_ServiceRequest_3_Structure_]
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply