Please help i am having the error below while exporting data to excel.
Please note i have the description column in 2 different table and i also want only the first record in job_material for each pid in job_material table to be displayed in the excel.
Error Message
The column 'description' was specified multiple times for 't'. Invalid column name 'id'.
I have five tables namely
1. job 2. job_cylinder 3. job_die 4.job_ink 5. job_material
Please note that
1.) The id in the job table(j) is the pid in the cylinder table (c) (inner join job_cylinder c on j.id =c.pid)
2.) The dierefernceno in the job table (j) the id in job_die table (d) ( inner join job_die d on j.dierefernceno=d.id)
3.) the id in the job table(j) is the pid in the job_ink table (i) (inner join job_ink i on j.id =i.pid)
4.) The id in the job table(j) is the pid in the job_material table (m) (inner join job_material m on j.id =m.pid)
USE [Test]
GO
/****** Object: Table [dbo].[job] Script Date: 23/10/2022 23:23:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[job](
[id] [int] NULL,
[pid] [nvarchar](50) NULL,
[description] [nvarchar](50) NULL,
[variant] [nvarchar](50) NULL,
[country] [nvarchar](50) NULL,
[pack] [nvarchar](50) NULL,
[customer] [nvarchar](50) NULL,
[artworkdate] [nvarchar](50) NULL,
[dierefernceno] [nvarchar](50) NULL,
[templateno] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[job_cylinder] Script Date: 23/10/2022 23:23:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[job_cylinder](
[id] [int] NULL,
[pid] [int] NULL,
[unit] [int] NULL,
[posino] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[job_die] Script Date: 23/10/2022 23:23:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[job_die](
[id] [int] NULL,
[dieno] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[job_ink] Script Date: 23/10/2022 23:23:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[job_ink](
[id] [int] NULL,
[pid] [int] NULL,
[unit] [int] NULL,
[description] [nvarchar](50) NULL,
[inkcode] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[job_material] Script Date: 23/10/2022 23:23:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[job_material](
[id] [int] NULL,
[pid] [int] NULL,
[materialcode] [nvarchar](50) NULL,
[boardname] [nvarchar](50) NULL,
[materialgsm] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[job] ([id], [pid], [description], [variant], [country], [pack], [customer], [artworkdate], [dierefernceno], [templateno]) VALUES (1, N'SP001', N'AB-CD-EF', N'TALL', N'SPAIN', N'24A', N'SONI', N'20/10/2022', N'1', N'2001A')
GO
INSERT [dbo].[job] ([id], [pid], [description], [variant], [country], [pack], [customer], [artworkdate], [dierefernceno], [templateno]) VALUES (2, N'SP002', N'GH-IJ-KL', N'SHORT', N'UK', N'24B', N'PANON', N'26/11/2013', N'2', N'2002B')
GO
INSERT [dbo].[job] ([id], [pid], [description], [variant], [country], [pack], [customer], [artworkdate], [dierefernceno], [templateno]) VALUES (3, N'SP003', N'MN-OP-QR', N'MEDIUM', N'USA', N'24C', N'LGE', N'20/9/2017', N'3', N'2003C')
GO
INSERT [dbo].[job] ([id], [pid], [description], [variant], [country], [pack], [customer], [artworkdate], [dierefernceno], [templateno]) VALUES (4, N'SP004', N'ST-UV-WX', N'TALL-SHORT', N'DENMARK', N'24D', N'HPA', N'18/6/2016', N'4', N'2004D')
GO
INSERT [dbo].[job_cylinder] ([id], [pid], [unit], [posino]) VALUES (1, 1, 1, N'A2300')
GO
INSERT [dbo].[job_cylinder] ([id], [pid], [unit], [posino]) VALUES (2, 1, 2, N'A2301')
GO
INSERT [dbo].[job_cylinder] ([id], [pid], [unit], [posino]) VALUES (3, 1, 3, N'A2302')
GO
INSERT [dbo].[job_cylinder] ([id], [pid], [unit], [posino]) VALUES (4, 1, 4, N'A2303')
GO
INSERT [dbo].[job_die] ([id], [dieno]) VALUES (1, N'D3900')
GO
INSERT [dbo].[job_die] ([id], [dieno]) VALUES (2, N'D4900')
GO
INSERT [dbo].[job_die] ([id], [dieno]) VALUES (3, N'D5900')
GO
INSERT [dbo].[job_die] ([id], [dieno]) VALUES (4, N'D6900')
GO
INSERT [dbo].[job_ink] ([id], [pid], [unit], [description], [inkcode]) VALUES (1, 1, 1, N'6700A', N'BC678')
GO
INSERT [dbo].[job_ink] ([id], [pid], [unit], [description], [inkcode]) VALUES (2, 1, 2, N'6700B', N'BD679')
GO
INSERT [dbo].[job_ink] ([id], [pid], [unit], [description], [inkcode]) VALUES (3, 1, 3, N'6700C', N'BD701')
GO
INSERT [dbo].[job_ink] ([id], [pid], [unit], [description], [inkcode]) VALUES (4, 1, 4, N'6700D', N'BD703')
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (1, 1, N'SNG111', N'SILBLUE', N'G2000')
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (2, 1, NULL, NULL, NULL)
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (3, 2, N'SNG222', N'GLDRED', N'G3000')
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (4, 2, NULL, NULL, NULL)
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (5, 3, N'SNG333', N'BLKBLUE', N'G4000')
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (6, 3, NULL, NULL, NULL)
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (7, 4, N'SNG444', N'YLWGRY', N'G5000')
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (8, 4, NULL, NULL, NULL)
GO
SELECT id, pid,description,variant,country,pack,customer,artworkdate,templateno,dieno,materialcode,boardname,materialgsm,
posi1=(select posino from job_Cylinder where unit='1'),inkcode1=(select inkcode from job_ink where unit='1'),
description1=(select description from job_ink where unit='1')
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY j.id ORDER BY (j.id)) RowNo,j.pid,j.description,j.variant,j.country,j.pack,j.customer,j.artworkdate,j.templateno,d.dieno,m.materialcode,m.boardname,m.materialgsm,c.posino,i.inkcode,i.description
from job j
inner join job_die d on j.dierefernceno=d.id
inner join job_material m on j.id = m.pid
inner join job_cylinder c on j.id =c.pid
inner join job_ink i on j.id=i.pid
) t
WHERE t.RowNo = 1
