Nested table in query

Question

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

and the query for review is

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

• Toby Speight: Please edit your question so that the title describes the purpose of the code, rather than its mechanism. We really need to understand the motivational context to give good reviews. Thanks!
• sgmoore: You probably need to explain what results you are expecting. The joins are (at least to me) rather usual. I presume you have some reason for them rather than a more normal syntax (like inner JOIN dbo.MaterialRequest ON dbo.MaterialRequest.MaterialReqNo = dbo.MaterialRequestDetails.MaterialRequestNo inner JOIN dbo.Item ON dbo.Item.ItemNo = dbo.MaterialRequestDetails.ItemNo inner JOIN dbo.Location ON dbo.Location.LocationNo = dbo.MaterialRequest.LocationNo inner JOIN dbo.Unit ON dbo.Unit.UnitNo = dbo.Item.UnitNo
• Basit Sarguroh: many thanks for reply, the query will show all record from Trn_MaterialRequest table and data related to the reference

Answer 1

Three part naming in the list of columns has been deprecated. You should become familiar with aliases and use them. It makes your code a lot easier to read.

That wall of text query would look something like this with aliases.

SELECT mr.MaterialReqNo
 , mr.MaterialReqNumber
 , mr.RequestDate
 , mr.LocationNo
 , mrd.MaterialReqDetailNo
 , mrd.ItemNo
 , mrd.Qty
 , i.ItemCode
 , i.ItemDescription
 , i.UnitNo
 , l.Location
 , u.Unit
FROM dbo.MaterialRequestDetails mrd
RIGHT OUTER JOIN dbo.Item i ON mrd.ItemNo = i.ItemNo 
LEFT OUTER JOIN dbo.Unit u ON i.UnitNo = u.UnitNo 
RIGHT OUTER JOIN dbo.Location l
LEFT OUTER JOIN dbo.MaterialRequest mr ON l.LocationNo = mr.LocationNo 
 ON mrd.MaterialRequestNo = mr.MaterialReqNo

Right joins are almost always a red flag that something is not quite right. They are unusual and uncommon for sure. Most people just don't think like that. There seems to be something a bit off in your query but not totally sure what you are trying to accomplish.

• Basit Sarguroh: Many thanks for your reply, 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