[Solved] SQL Server Inner Join Error: The column description was specified multiple times for t

lingers

SOLVED CLOSED ACTIVE ANSWERED
User: lingers
Posted: on Oct 25, 2022 10:56 PM
Forum: SQL Server
Answer: 1
Views: 2013

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

Download FREE API for Word, Excel and PDF in ASP.Net

PrinceG

0 9

Reply
ANSWER
User: PrinceG
Replied: on Oct 26, 2022 07:41 AM
Modified: on Oct 26, 2022 08:01 AM
Report

Hi lingers,

description column specified multiple times in the select query. So you need to specify alias for each description column or remove one description column from the query.

Refere this SQL Query.

SQL

CREATE TABLE [job]
(
	[id] INT NULL,
	[pid] VARCHAR(50) NULL,
	[description] VARCHAR(50) NULL,
	[variant] VARCHAR(50) NULL,
	[country] VARCHAR(50) NULL,
	[pack] VARCHAR(50) NULL,
	[customer] VARCHAR(50) NULL,
	[artworkdate] VARCHAR(50) NULL,
	[dierefernceno] VARCHAR(50) NULL,
	[templateno] VARCHAR(50) NULL
) 
INSERT [job] ([id], [pid], [description], [variant], [country], [pack], [customer], [artworkdate], [dierefernceno], [templateno]) VALUES (1, 'SP001', 'AB-CD-EF', 'TALL', 'SPAIN', '24A', 'SONI', '20/10/2022', '1', '2001A')
INSERT [job] ([id], [pid], [description], [variant], [country], [pack], [customer], [artworkdate], [dierefernceno], [templateno]) VALUES (2, 'SP002', 'GH-IJ-KL', 'SHORT', 'UK', '24B', 'PANON', '26/11/2013', '2', '2002B')
INSERT [job] ([id], [pid], [description], [variant], [country], [pack], [customer], [artworkdate], [dierefernceno], [templateno]) VALUES (3, 'SP003', 'MN-OP-QR', 'MEDIUM', 'USA', '24C', 'LGE', '20/9/2017', '3', '2003C')
INSERT [job] ([id], [pid], [description], [variant], [country], [pack], [customer], [artworkdate], [dierefernceno], [templateno]) VALUES (4, 'SP004', 'ST-UV-WX', 'TALL-SHORT', 'DENMARK', '24D', 'HPA', N'18/6/2016', '4', '2004D')

CREATE TABLE [job_cylinder]
(
	[id] [int] NULL,
	[pid] [int] NULL,
	[unit] [int] NULL,
	[posino] VARCHAR(50) NULL
) 
INSERT [job_cylinder] ([id], [pid], [unit], [posino]) VALUES (1, 1, 1, 'A2300')
INSERT [job_cylinder] ([id], [pid], [unit], [posino]) VALUES (2, 2, 2, 'A2301')
INSERT [job_cylinder] ([id], [pid], [unit], [posino]) VALUES (3, 3, 3, 'A2302')
INSERT [job_cylinder] ([id], [pid], [unit], [posino]) VALUES (4, 4, 4, 'A2303')

CREATE TABLE [job_die]
(
	[id] [int] NULL,
	[dieno] VARCHAR(50) NULL
)
INSERT [job_die] ([id], [dieno]) VALUES (1, 'D3900')
INSERT [job_die] ([id], [dieno]) VALUES (2, 'D4900')
INSERT [job_die] ([id], [dieno]) VALUES (3, 'D5900')
INSERT [job_die] ([id], [dieno]) VALUES (4, 'D6900')
 

CREATE TABLE [job_ink]
(
	[id] [int] NULL,
	[pid] [int] NULL,
	[unit] [int] NULL,
	[description] VARCHAR(50) NULL,
	[inkcode] VARCHAR(50) NULL
)
INSERT [job_ink] ([id], [pid], [unit], [description], [inkcode]) VALUES (1, 1, 1, '6700A', 'BC678')
INSERT [job_ink] ([id], [pid], [unit], [description], [inkcode]) VALUES (2, 2, 2, '6700B', 'BD679')
INSERT [job_ink] ([id], [pid], [unit], [description], [inkcode]) VALUES (3, 3, 3, '6700C', 'BD701')
INSERT [job_ink] ([id], [pid], [unit], [description], [inkcode]) VALUES (4, 4, 4, '6700D', 'BD703')


CREATE TABLE [job_material]
(
	[id] [int] NULL,
	[pid] [int] NULL,
	[materialcode] VARCHAR(50) NULL,
	[boardname] VARCHAR(50) NULL,
	[materialgsm] VARCHAR(50) NULL
)
INSERT [job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (1, 1, 'SNG111', 'SILBLUE', 'G2000')
INSERT [job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (2, 1, NULL, NULL, NULL)
INSERT [job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (3, 2, 'SNG222', 'GLDRED', 'G3000')
INSERT [job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (4, 2, NULL, NULL, NULL)
INSERT [job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (5, 3, 'SNG333', 'BLKBLUE', 'G4000')
INSERT [job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (6, 3, NULL, NULL, NULL)
INSERT [job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (7, 4, 'SNG444', 'YLWGRY', 'G5000')
INSERT [job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (8, 4, NULL, NULL, NULL)

SELECT [id], [pid], [description], [variant], [country], [pack], [customer], 
		[artworkdate], [dierefernceno], [templateno], [dieno], [materialcode], [boardname],[materialgsm],
posi1=(SELECT DISTINCT [posino] FROM [job_Cylinder] WHERE unit='1'),
inkcode1=(SELECT DISTINCT [inkcode] FROM [job_ink] WHERE unit='1'),
description1=(SELECT DISTINCT [description] FROM [job_ink] WHERE unit='1')
FROM
(
	SELECT ROW_NUMBER() OVER(PARTITION BY j.[pid] ORDER BY (j.[id])) RowNo,
			j.[id],j.[pid],j.[description],j.[variant],j.[country],j.[pack],j.[customer],
			j.[artworkdate],j.[dierefernceno],j.[templateno],d.[dieno],
			m.[materialcode],m.[boardname],m.[materialgsm],c.[posino],i.[inkcode]
	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

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

[Solved] SQL Server Inner Join Error: The column description was specified multiple times for t

Company

Explore

Follow us

Contact Us