Select First Row in Each GROUP with Join query in SQL Server

lingers

SOLVED CLOSED ACTIVE ANSWERED
User: lingers
Posted: on Oct 18, 2022 09:39 PM
Forum: SQL Server
Answer: 1
Views: 1339

I have 3 table namely Customers,Interest and staff. i used a join statement to export data from this 3 table to an excel sheet which is working fine. But the problem is that the interest table has 2 values which are in 2 rows for each customer. I only want to display the first row in the join statement. The display currently display 2 records in the excel sheet.

My tables

https://ctrl.vi/i/BI6dt5_7E

what it is dispalying

https://ctrl.vi/i/BSWcPZ2P3

What i want

https://ctrl.vi/i/5AWS6ZQrS

please help

USE [Test]
GO
/****** Object:  Table [dbo].[Customers]    Script Date: 18/10/2022 22:43:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customers](
	[CustomerID] [nvarchar](50) NOT NULL,
	[ContactName] [nvarchar](50) NOT NULL,
	[City] [nvarchar](50) NOT NULL,
	[Country] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Interest]    Script Date: 18/10/2022 22:43:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Interest](
	[Workid] [nvarchar](50) NOT NULL,
	[Hobbies] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Staff]    Script Date: 18/10/2022 22:43:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Staff](
	[Workid] [nvarchar](50) NOT NULL,
	[Age] [nvarchar](50) NOT NULL,
	[Occupation] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Customers] ([CustomerID], [ContactName], [City], [Country]) VALUES (N'51', N'Aaron', N'Newyork', N'USA')
GO
INSERT [dbo].[Customers] ([CustomerID], [ContactName], [City], [Country]) VALUES (N'52', N'Angela', N'Rome', N'ITALY')
GO
INSERT [dbo].[Customers] ([CustomerID], [ContactName], [City], [Country]) VALUES (N'53', N'Luke', N'Ibiza', N'SPAIN')
GO
INSERT [dbo].[Customers] ([CustomerID], [ContactName], [City], [Country]) VALUES (N'54', N'Ramson', N'London', N'UK')
GO
INSERT [dbo].[Customers] ([CustomerID], [ContactName], [City], [Country]) VALUES (N'55', N'Merlin', N'Munich', N'GERMANY')
GO
INSERT [dbo].[Interest] ([Workid], [Hobbies]) VALUES (N'51', N'Travellin')
GO
INSERT [dbo].[Interest] ([Workid], [Hobbies]) VALUES (N'51', N'Reading')
GO
INSERT [dbo].[Interest] ([Workid], [Hobbies]) VALUES (N'52', N'Sleeping')
GO
INSERT [dbo].[Interest] ([Workid], [Hobbies]) VALUES (N'52', N'Eating')
GO
INSERT [dbo].[Interest] ([Workid], [Hobbies]) VALUES (N'53', N'Swiming')
GO
INSERT [dbo].[Interest] ([Workid], [Hobbies]) VALUES (N'53 ', N'Running')
GO
INSERT [dbo].[Interest] ([Workid], [Hobbies]) VALUES (N'54 ', N'Dancing')
GO
INSERT [dbo].[Interest] ([Workid], [Hobbies]) VALUES (N'54', N'Driving')
GO
INSERT [dbo].[Interest] ([Workid], [Hobbies]) VALUES (N'55', N'Diving')
GO
INSERT [dbo].[Interest] ([Workid], [Hobbies]) VALUES (N'55', N'Hunting')
GO
INSERT [dbo].[Staff] ([Workid], [Age], [Occupation]) VALUES (N'51', N'56', N'Driver')
GO
INSERT [dbo].[Staff] ([Workid], [Age], [Occupation]) VALUES (N'52', N'44', N'Pilot')
GO
INSERT [dbo].[Staff] ([Workid], [Age], [Occupation]) VALUES (N'53', N'67', N'Teacher')
GO
INSERT [dbo].[Staff] ([Workid], [Age], [Occupation]) VALUES (N'54', N'45', N'Doctor')
GO
INSERT [dbo].[Staff] ([Workid], [Age], [Occupation]) VALUES (N'55', N'36', N'Nurse')
GO

please help

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

PrinceG

0 2

Reply
ANSWER
User: PrinceG
Replied: on Oct 19, 2022 06:32 AM
Modified: on Oct 19, 2022 06:34 AM
Report

Hi lingers,

Please refer below SQL Query.

SQL

CREATE TABLE #Customers
(
    [CustomerID] [nvarchar](50) NOT NULL,
    [ContactName] [nvarchar](50) NOT NULL,
    [City] [nvarchar](50) NOT NULL,
    [Country] [nvarchar](50) NOT NULL
)

INSERT #Customers ([CustomerID], [ContactName], [City], [Country]) VALUES ('51', 'Aaron', 'Newyork', 'USA')
INSERT #Customers ([CustomerID], [ContactName], [City], [Country]) VALUES ('52', 'Angela', 'Rome', 'ITALY')
INSERT #Customers ([CustomerID], [ContactName], [City], [Country]) VALUES ('53', 'Luke', 'Ibiza', 'SPAIN')
INSERT #Customers ([CustomerID], [ContactName], [City], [Country]) VALUES ('54', 'Ramson', 'London', 'UK')
INSERT #Customers ([CustomerID], [ContactName], [City], [Country]) VALUES ('55', 'Merlin', 'Munich', 'GERMANY')

CREATE TABLE #Interest
(
    [Workid] [nvarchar](50) NOT NULL,
    [Hobbies] [nvarchar](50) NOT NULL
) 

INSERT #Interest ([Workid], [Hobbies]) VALUES ('51', 'Travellin')
INSERT #Interest ([Workid], [Hobbies]) VALUES ('51', 'Reading')
INSERT #Interest ([Workid], [Hobbies]) VALUES ('52', 'Sleeping')
INSERT #Interest ([Workid], [Hobbies]) VALUES ('52', 'Eating')
INSERT #Interest ([Workid], [Hobbies]) VALUES ('53', 'Swiming')
INSERT #Interest ([Workid], [Hobbies]) VALUES ('53 ', 'Running')
INSERT #Interest ([Workid], [Hobbies]) VALUES ('54 ', 'Dancing')
INSERT #Interest ([Workid], [Hobbies]) VALUES ('54', 'Driving')
INSERT #Interest ([Workid], [Hobbies]) VALUES ('55', 'Diving')
INSERT #Interest ([Workid], [Hobbies]) VALUES ('55', 'Hunting')

CREATE TABLE #Staff
(
    [Workid] [nvarchar](50) NOT NULL,
    [Age] [nvarchar](50) NOT NULL,
    [Occupation] [nvarchar](50) NOT NULL
) 

INSERT #Staff ([Workid], [Age], [Occupation]) VALUES ('51', '56', 'Driver')
INSERT #Staff ([Workid], [Age], [Occupation]) VALUES ('52', '44', 'Pilot')
INSERT #Staff ([Workid], [Age], [Occupation]) VALUES ('53', '67', 'Teacher')
INSERT #Staff ([Workid], [Age], [Occupation]) VALUES ('54', '45', 'Doctor')
INSERT #Staff ([Workid], [Age], [Occupation]) VALUES ('55', '36', 'Nurse')

SELECT CustomerID, ContactName, City, Country,Age,Occupation,Hobbies
FROM 
(
SELECT 
	ROW_NUMBER() OVER(PARTITION BY c.CustomerID ORDER BY (c.CustomerID)) RowNo,
	c.CustomerID, c.ContactName, c.City, c.Country,s.Age,s.Occupation,i.Hobbies
FROM #Customers c 
INNER JOIN #Staff s ON c.CustomerID = s.Workid 
INNER JOIN #Interest i ON  c.CustomerID = i.Workid) t
WHERE t.RowNo = 1

DROP TABLE #Customers
DROP TABLE #Interest
DROP TABLE #Staff

Output

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

Select First Row in Each GROUP with Join query in SQL Server

Company

Explore

Follow us

Contact Us