Query require

basit0079 · March 13, 2023, 6:19pm

I have three table, Supplier, PO and PO Details.

I want to get the records when user pass the PO_Number then on that PO_Number whatever item code available, that item code and previous purchase item code should show.

below is the table structure.

CREATE TABLE [dbo].[Supplier](
	[Supplier_No] [int] NOT NULL,
	[SupplierName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Supplier] PRIMARY KEY CLUSTERED 
(
	[Supplier_No] 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].[PO](
	[Po_No] [int] IDENTITY(1,1) NOT NULL,
	[PO_Number] [nvarchar](50) NULL,
	[PO_Date] [date] NULL,
	[SupplierNo] [int] NULL,
 CONSTRAINT [PK_PO] PRIMARY KEY CLUSTERED 
(
	[Po_No] 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].[PO]  WITH CHECK ADD  CONSTRAINT [FK_PO_Supplier] FOREIGN KEY([SupplierNo])
REFERENCES [dbo].[Supplier] ([Supplier_No])
GO

ALTER TABLE [dbo].[PO] CHECK CONSTRAINT [FK_PO_Supplier]
GO

CREATE TABLE [dbo].[PODetails](
	[PO_No] [int] NULL,
	[ItemCode] [nvarchar](50) NULL,
	[ItemDescription] [nvarchar](50) NULL,
	[Qty] [decimal](18, 2) NULL,
	[UnitPrice] [decimal](18, 2) NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[PODetails]  WITH CHECK ADD  CONSTRAINT [FK_PODetails_PO] FOREIGN KEY([PO_No])
REFERENCES [dbo].[PO] ([Po_No])
GO

ALTER TABLE [dbo].[PODetails] CHECK CONSTRAINT [FK_PODetails_PO]
GO

example user pass the PO Number = 123

then first get what are the item on that PO. example got three item

Item =A
Item = B
Item=C

then get previous all the purchase related to Item=A, Item=B and ItemC

Item	PO No	Supplier	Qty	Unit Price
ItemA	123	A	1	12
ItemA	22	B	2	13
ItemB	123	A	1	66
ItemB	34	C	1	17
ItemC	123	A	1	2
ItemC	345	D	1	4

Thanks & Regards,
Basit.

harishgg1 · March 14, 2023, 6:06am

Provide Sample Data DDL

basit0079 · March 14, 2023, 7:41am

Many Thanks for your reply.
Kindly find the below queries.

CREATE TABLE [dbo].[Supplier](
	[Supplier_No] [int] IDENTITY(1,1) NOT NULL,
	[SupplierName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Supplier] PRIMARY KEY CLUSTERED 
(
	[Supplier_No] 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

INSERT INTO [a].[dbo].[Supplier]([SupplierName]) VALUES('A')
INSERT INTO [a].[dbo].[Supplier]([SupplierName]) VALUES('B')
INSERT INTO [a].[dbo].[Supplier]([SupplierName]) VALUES('C')
INSERT INTO [a].[dbo].[Supplier]([SupplierName]) VALUES('D')


CREATE TABLE [dbo].[PO](
	[Po_No] [int] IDENTITY(1,1) NOT NULL,
	[PO_Number] [nvarchar](50) NULL,
	[PO_Date] [date] NULL,
	[SupplierNo] [int] NULL,
 CONSTRAINT [PK_PO] PRIMARY KEY CLUSTERED 
(
	[Po_No] 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].[PO]  WITH CHECK ADD  CONSTRAINT [FK_PO_Supplier] FOREIGN KEY([SupplierNo])
REFERENCES [dbo].[Supplier] ([Supplier_No])
GO

ALTER TABLE [dbo].[PO] CHECK CONSTRAINT [FK_PO_Supplier]
GO


INSERT INTO [a].[dbo].[PO]([PO_Number],[PO_Date],[SupplierNo])
     VALUES(123,'2022-01-01',1)
     INSERT INTO [a].[dbo].[PO]([PO_Number],[PO_Date],[SupplierNo])
     VALUES(22,'2022-01-02',2)
     INSERT INTO [a].[dbo].[PO]([PO_Number],[PO_Date],[SupplierNo])
     VALUES(34,'2022-01-03',3)
INSERT INTO [a].[dbo].[PO]([PO_Number],[PO_Date],[SupplierNo])
     VALUES(345,'2022-01-04',4)
     
     CREATE TABLE [dbo].[PODetails](
	[PO_No] [int] NULL,
	[ItemCode] [nvarchar](50) NULL,
	[ItemDescription] [nvarchar](50) NULL,
	[Qty] [decimal](18, 2) NULL,
	[UnitPrice] [decimal](18, 2) NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[PODetails]  WITH CHECK ADD  CONSTRAINT [FK_PODetails_PO] FOREIGN KEY([PO_No])
REFERENCES [dbo].[PO] ([Po_No])
GO

ALTER TABLE [dbo].[PODetails] CHECK CONSTRAINT [FK_PODetails_PO]
GO


INSERT INTO [a].[dbo].[PODetails]([PO_No],[ItemCode],[ItemDescription],[Qty],[UnitPrice])
     VALUES (1,'1111','ItemA',1,45)
     
     
INSERT INTO [a].[dbo].[PODetails]([PO_No],[ItemCode],[ItemDescription],[Qty],[UnitPrice])
     VALUES (1,'2222','ItemB',1,66)

INSERT INTO [a].[dbo].[PODetails]([PO_No],[ItemCode],[ItemDescription],[Qty],[UnitPrice])
     VALUES (1,'3333','ItemC',1,66)

INSERT INTO [a].[dbo].[PODetails]([PO_No],[ItemCode],[ItemDescription],[Qty],[UnitPrice])
     VALUES (2,'1111','ItemA',3,87)
    
    INSERT INTO [a].[dbo].[PODetails]([PO_No],[ItemCode],[ItemDescription],[Qty],[UnitPrice])
     VALUES (2,'2222','ItemB',1,33)
     
     
     INSERT INTO [a].[dbo].[PODetails]([PO_No],[ItemCode],[ItemDescription],[Qty],[UnitPrice])
     VALUES (3,'3333','ItemC',1,99)

 Example if pass below query.

SELECT     dbo.PO.PO_Number, dbo.PODetails.ItemCode, dbo.PODetails.ItemDescription, dbo.PODetails.Qty, dbo.PODetails.UnitPrice
FROM         dbo.PODetails INNER JOIN
                      dbo.PO ON dbo.PODetails.PO_No = dbo.PO.Po_No LEFT OUTER JOIN
                      dbo.Supplier ON dbo.PO.SupplierNo = dbo.Supplier.Supplier_No
WHERE     (dbo.PO.PO_Number = N'123')

then below is the output

PO_Number	ItemCode	ItemDescription	Qty	UnitPrice
123	1111	ItemA	1	45
123	2222	ItemB	1	66
123	3333	ItemC	1	66

looking for output is also show ItemA, ItemB and ItemC which purchase earlier in other Po's.

example in PO if there are two items then related to that two items shows when its previously purchase with their PO number and qty and Unit Price.

Thanks once again

harishgg1 · March 14, 2023, 2:13pm

hi

hope this helps

create data script

-- ALTER TABLE [dbo].[PODetails] DROP CONSTRAINT [FK_PODetails_PO]

--ALTER TABLE [dbo].[PO] DROP CONSTRAINT [FK_PO_Supplier]

drop table if exists [dbo].[Supplier]
CREATE TABLE [dbo].[Supplier](
[Supplier_No] [int] IDENTITY(1,1) NOT NULL,
[SupplierName] nvarchar NULL,
CONSTRAINT [PK_Supplier] PRIMARY KEY CLUSTERED
(
[Supplier_No] 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

INSERT INTO [dbo].Supplier VALUES('A')
INSERT INTO [dbo].Supplier VALUES('B')
INSERT INTO [dbo].Supplier VALUES('C')
INSERT INTO [dbo].Supplier VALUES('D')

drop table if exists [dbo].[PO]
CREATE TABLE [dbo].[PO](
[Po_No] [int] IDENTITY(1,1) NOT NULL,
[PO_Number] nvarchar NULL,
[PO_Date] [date] NULL,
[SupplierNo] [int] NULL,
CONSTRAINT [PK_PO] PRIMARY KEY CLUSTERED
(
[Po_No] 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].[PO] WITH CHECK ADD CONSTRAINT [FK_PO_Supplier] FOREIGN KEY([SupplierNo])
REFERENCES [dbo].[Supplier] ([Supplier_No])
GO

ALTER TABLE [dbo].[PO] CHECK CONSTRAINT [FK_PO_Supplier]
GO

INSERT INTO [dbo].PO
VALUES(123,'2022-01-01',1)
INSERT INTO [dbo].PO
VALUES(22,'2022-01-02',2)
INSERT INTO [dbo].PO
VALUES(34,'2022-01-03',3)
INSERT INTO [dbo].PO
VALUES(345,'2022-01-04',4)

drop table if exists [dbo].[PODetails]
CREATE TABLE [dbo].[PODetails](
[PO_No] [int] NULL,
[ItemCode] nvarchar NULL,
[ItemDescription] nvarchar NULL,
[Qty] [decimal](18, 2) NULL,
[UnitPrice] [decimal](18, 2) NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[PODetails] WITH CHECK ADD CONSTRAINT [FK_PODetails_PO] FOREIGN KEY([PO_No])
REFERENCES [dbo].[PO] ([Po_No])
GO

ALTER TABLE [dbo].[PODetails] CHECK CONSTRAINT [FK_PODetails_PO]
GO

INSERT INTO [dbo].PODetails
VALUES (1,'1111','ItemA',1,45)

INSERT INTO [dbo].PODetails
VALUES (1,'2222','ItemB',1,66)

INSERT INTO [dbo].PODetails
VALUES (1,'3333','ItemC',1,66)

INSERT INTO [dbo].PODetails
VALUES (2,'1111','ItemA',3,87)

INSERT INTO [dbo].PODetails
VALUES (2,'2222','ItemB',1,33)

INSERT INTO [dbo].PODetails
VALUES (3,'3333','ItemC',1,99)

DECLARE @PO_Number int = 123 

SELECT  
      a.ItemDescription 
	, b.PO_Number 
	, c.SupplierName 
	,  a.Qty 
	, a.UnitPrice 
FROM   
    PODetails a 
	  JOIN 
	po b on a.PO_No = b.Po_No 
	  JOIN 
	Supplier c on b.SupplierNo= c.Supplier_No
WHERE
    ItemCode in (select  ItemCode from po a join PODetails b on a.po_no = b.po_no where PO_Number = @PO_Number)
ORDER BY 
   ItemDescription

basit0079 · March 15, 2023, 5:13am

Many Thanks your help. Great.
Thanks & regards,