0
\$\begingroup\$

I have transaction tables calledMaterialRequest andMaterialRequestDetails.

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]GOCREATE 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]GOCREATE 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]GOALTER TABLE [dbo].[Item]  WITH CHECK ADD  CONSTRAINT [FK_Item_Unit] FOREIGN KEY([UnitNo])REFERENCES [dbo].[Unit] ([UnitNo])GOALTER TABLE [dbo].[Item] CHECK CONSTRAINT [FK_Item_Unit]GOCREATE 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]GOALTER TABLE [dbo].[MaterialRequest]  WITH CHECK ADD  CONSTRAINT [FK_MaterialRequest_Location] FOREIGN KEY([LocationNo])REFERENCES [dbo].[Location] ([LocationNo])GOALTER TABLE [dbo].[MaterialRequest] CHECK CONSTRAINT [FK_MaterialRequest_Location]GOCREATE 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]GOALTER TABLE [dbo].[MaterialRequestDetails]  WITH CHECK ADD  CONSTRAINT [FK_MaterialRequestDetails_Item] FOREIGN KEY([ItemNo])REFERENCES [dbo].[Item] ([ItemNo])GOALTER TABLE [dbo].[MaterialRequestDetails] CHECK CONSTRAINT [FK_MaterialRequestDetails_Item]GOALTER TABLE [dbo].[MaterialRequestDetails]  WITH CHECK ADD  CONSTRAINT [FK_MaterialRequestDetails_MaterialRequest] FOREIGN KEY([MaterialRequestNo])REFERENCES [dbo].[MaterialRequest] ([MaterialReqNo])GOALTER 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.UnitFROM         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
askedSep 12, 2022 at 5:50
Basit Sarguroh's user avatar
\$\endgroup\$
3
  • 2
    \$\begingroup\$Pleaseedit your question so that the title describes thepurpose of the code, rather than itsmechanism. We really need to understand the motivational context to give good reviews. Thanks!\$\endgroup\$CommentedSep 12, 2022 at 11:04
  • 1
    \$\begingroup\$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 (likeinner 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\$\endgroup\$CommentedSep 12, 2022 at 11:43
  • \$\begingroup\$many thanks for reply, the query will show all record from Trn_MaterialRequest table and data related to the reference\$\endgroup\$CommentedSep 13, 2022 at 5:27

1 Answer1

1
\$\begingroup\$

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.UnitFROM dbo.MaterialRequestDetails mrdRIGHT 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 lLEFT 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.

answeredSep 27, 2022 at 14:36
Sean Lange's user avatar
\$\endgroup\$
1
  • \$\begingroup\$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\$\endgroup\$CommentedSep 29, 2022 at 9:36

You mustlog in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.