Query to combine two tables based a third table!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query to combine two tables based a third table!!

Author

Topic

nguyenanhminhxd
Starting Member

3 Posts

Posted - 2015-02-18 : 09:05:35

I have three tables A, B, C respectively. Table C is used to map table A and B. Three tables are below:
Table A:

Table B:

Table C:

So what query do I need write to have table like below?

Thanks in advance!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-18 : 09:49:57

Please post your tables as CREATE TABLE statements

nguyenanhminhxd
Starting Member

3 Posts

Posted - 2015-02-18 : 09:59:30

quote:
Originally posted by gbritton

Please post your tables as CREATE TABLE statements

not really sure what you mean, but below is my script to create three tables:

USE [Sample]
GO
/****** Object: Table [dbo].[TableA] Script Date: 2/18/2015 6:49:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TableA](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
[PhoneNumber] [int] NOT NULL,
CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED
(
[Id] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[TableB] Script Date: 2/18/2015 6:49:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TableB](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Task] [varchar](50) NOT NULL,
CONSTRAINT [PK_TableB] PRIMARY KEY CLUSTERED
(
[Id] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[TableC] Script Date: 2/18/2015 6:49:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TableC](
[Id] [int] IDENTITY(1,1) NOT NULL,
[A's Id] [int] NOT NULL,
[B's Id] [int] NOT NULL,
CONSTRAINT [PK_TableC] PRIMARY KEY CLUSTERED
(
[Id] 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
SET IDENTITY_INSERT [dbo].[TableA] ON

INSERT [dbo].[TableA] ([Id], [FirstName], [LastName], [PhoneNumber]) VALUES (1, N'aaa', N'bbb', 1111)
INSERT [dbo].[TableA] ([Id], [FirstName], [LastName], [PhoneNumber]) VALUES (2, N'ccc', N'ddd', 2222)
INSERT [dbo].[TableA] ([Id], [FirstName], [LastName], [PhoneNumber]) VALUES (3, N'eee', N'fff', 3333)
INSERT [dbo].[TableA] ([Id], [FirstName], [LastName], [PhoneNumber]) VALUES (4, N'hhh', N'ggg', 4444)
SET IDENTITY_INSERT [dbo].[TableA] OFF
SET IDENTITY_INSERT [dbo].[TableB] ON

INSERT [dbo].[TableB] ([Id], [Task]) VALUES (1, N'clean house')
INSERT [dbo].[TableB] ([Id], [Task]) VALUES (2, N'wash dishes')
INSERT [dbo].[TableB] ([Id], [Task]) VALUES (3, N'go shopping')
INSERT [dbo].[TableB] ([Id], [Task]) VALUES (4, N'cooking')
SET IDENTITY_INSERT [dbo].[TableB] OFF
SET IDENTITY_INSERT [dbo].[TableC] ON

INSERT [dbo].[TableC] ([Id], [A's Id], [B's Id]) VALUES (1, 1, 2)
INSERT [dbo].[TableC] ([Id], [A's Id], [B's Id]) VALUES (2, 1, 1)
INSERT [dbo].[TableC] ([Id], [A's Id], [B's Id]) VALUES (3, 2, 3)
INSERT [dbo].[TableC] ([Id], [A's Id], [B's Id]) VALUES (5, 3, 1)
INSERT [dbo].[TableC] ([Id], [A's Id], [B's Id]) VALUES (7, 3, 4)
INSERT [dbo].[TableC] ([Id], [A's Id], [B's Id]) VALUES (8, 4, 1)
SET IDENTITY_INSERT [dbo].[TableC] OFF
ALTER TABLE [dbo].[TableC] WITH CHECK ADD CONSTRAINT [FK_TableC_TableA] FOREIGN KEY([A's Id])
REFERENCES [dbo].[TableA] ([Id])
GO
ALTER TABLE [dbo].[TableC] CHECK CONSTRAINT [FK_TableC_TableA]
GO
ALTER TABLE [dbo].[TableC] WITH CHECK ADD CONSTRAINT [FK_TableC_TableB] FOREIGN KEY([B's Id])
REFERENCES [dbo].[TableB] ([Id])
GO
ALTER TABLE [dbo].[TableC] CHECK CONSTRAINT [FK_TableC_TableB]
GO

barnabeck
Posting Yak Master

236 Posts

Posted - 2015-02-19 : 06:40:21

not having the tables I can't check if it's working, but it must be something like:

SELECT FirstName + ' ' + LastName as FullName, PhoneNumber, 
       stuff((Select ', ' + Task 
              from TABLEB inner join TABLEC on TABLEB.ID = TABLEC.BID 
              where TABLEC.AID = TableA.ID
              GROUP BY Task for xml path('')),1,1,'') AS Tasks
from TABLEA

Martin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources