I have transaction tables called MaterialRequest and MaterialRequestDetails.
What I want to check my query and review that it's correct way to do this.
Below are tables.
CREATE TABLE [dbo].[Unit](
[UnitNo] [int] IDENTITY(1,1) NOT NULL,
[Unit] [nvarchar](50) NULL,
CONSTRAINT [PK_Unit] PRIMARY KEY CLUSTERED
(
[UnitNo] 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
CREATE TABLE [dbo].[Location](
[LocationNo] [int] IDENTITY(1,1) NOT NULL,
[Location] [nvarchar](50) NULL,
CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED
(
[LocationNo] 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
CREATE TABLE [dbo].[Item](
[ItemNo] [int] IDENTITY(1,1) NOT NULL,
[ItemCode] [nvarchar](50) NULL,
[ItemDescription] [nvarchar](500) NULL,
[UnitNo] [int] NULL,
CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED
(
[ItemNo] 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].[Item] WITH CHECK ADD CONSTRAINT [FK_Item_Unit] FOREIGN KEY([UnitNo])
REFERENCES [dbo].[Unit] ([UnitNo])
GO
ALTER TABLE [dbo].[Item] CHECK CONSTRAINT [FK_Item_Unit]
GO
CREATE TABLE [dbo].[MaterialRequest](
[MaterialReqNo] [int] IDENTITY(1,1) NOT NULL,
[MaterialReqNumber] [nvarchar](50) NULL,
[RequestDate] [date] NULL,
[LocationNo] [int] NULL,
CONSTRAINT [PK_MaterialRequest] PRIMARY KEY CLUSTERED
(
[MaterialReqNo] 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].[MaterialRequest] WITH CHECK ADD CONSTRAINT [FK_MaterialRequest_Location] FOREIGN KEY([LocationNo])
REFERENCES [dbo].[Location] ([LocationNo])
GO
ALTER TABLE [dbo].[MaterialRequest] CHECK CONSTRAINT [FK_MaterialRequest_Location]
GO
CREATE TABLE [dbo].[MaterialRequestDetails](
[MaterialReqDetailNo] [int] IDENTITY(1,1) NOT NULL,
[MaterialRequestNo] [int] NULL,
[ItemNo] [int] NULL,
[Qty] [numeric](18, 3) NULL,
CONSTRAINT [PK_MaterialRequestDetails] PRIMARY KEY CLUSTERED
(
[MaterialReqDetailNo] 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].[MaterialRequestDetails] WITH CHECK ADD CONSTRAINT [FK_MaterialRequestDetails_Item] FOREIGN KEY([ItemNo])
REFERENCES [dbo].[Item] ([ItemNo])
GO
ALTER TABLE [dbo].[MaterialRequestDetails] CHECK CONSTRAINT [FK_MaterialRequestDetails_Item]
GO
ALTER TABLE [dbo].[MaterialRequestDetails] WITH CHECK ADD CONSTRAINT [FK_MaterialRequestDetails_MaterialRequest] FOREIGN KEY([MaterialRequestNo])
REFERENCES [dbo].[MaterialRequest] ([MaterialReqNo])
GO
ALTER TABLE [dbo].[MaterialRequestDetails] CHECK CONSTRAINT [FK_MaterialRequestDetails_MaterialRequest]
GO
The Query i want to check is correct way is to doing
SELECT dbo.MaterialRequest.MaterialReqNo, dbo.MaterialRequest.MaterialReqNumber, dbo.MaterialRequest.RequestDate, dbo.MaterialRequest.LocationNo,
dbo.MaterialRequestDetails.MaterialReqDetailNo, dbo.MaterialRequestDetails.ItemNo, dbo.MaterialRequestDetails.Qty, dbo.Item.ItemCode, dbo.Item.ItemDescription,
dbo.Item.UnitNo, dbo.Location.Location, dbo.Unit.Unit
FROM dbo.MaterialRequestDetails RIGHT OUTER JOIN
dbo.Item LEFT OUTER JOIN
dbo.Unit ON dbo.Item.UnitNo = dbo.Unit.UnitNo ON dbo.MaterialRequestDetails.ItemNo = dbo.Item.ItemNo RIGHT OUTER JOIN
dbo.Location LEFT OUTER JOIN
dbo.MaterialRequest ON dbo.Location.LocationNo = dbo.MaterialRequest.LocationNo ON
dbo.MaterialRequestDetails.MaterialRequestNo = dbo.MaterialRequest.MaterialReqNo
what i want to achieve is Get all the records from master table. example i have transaction tables MaterialRequest and MaterialRequestDetails, i want to write the query to show me all the records which is available on MaterialRequest and MaterialRequestDetails as FK reference and join them into master table to get the master data
regards,
Basit.