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

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.