Query require

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.

Provide Sample Data DDL

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

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

image

1 Like

Many Thanks your help. Great.
Thanks & regards,