get results from 2 tables - SQL Server Forums

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

SQL Server 2012 Forums

Transact-SQL (2012)

get results from 2 tables

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-05-11 : 05:02:44

Hi,

I have 2 tables. Temp and PolicyDetails. I have to return according to customers all what is in temp but also have policy with coverage 234 in policyDetails.
Result:
customerid policyid coverageid
1 1 80
1 2 234
4 3 234
7 4 180
7 5 234
6 4 234

USE [TestDB]
GO
/****** Object:  Table [dbo].[temp]    Script Date: 05/11/2013 12:02:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[temp](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[CustomerID] [int] NULL,
	[PolicyId] [int] NULL,
 CONSTRAINT [PK_temp] 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].[temp] ON
INSERT [dbo].[temp] ([id], [CustomerID], [PolicyId]) VALUES (1, 1, 1)
INSERT [dbo].[temp] ([id], [CustomerID], [PolicyId]) VALUES (2, 2, 1)
INSERT [dbo].[temp] ([id], [CustomerID], [PolicyId]) VALUES (3, 3, 2)
INSERT [dbo].[temp] ([id], [CustomerID], [PolicyId]) VALUES (4, 4, 3)
INSERT [dbo].[temp] ([id], [CustomerID], [PolicyId]) VALUES (5, 7, 5)
INSERT [dbo].[temp] ([id], [CustomerID], [PolicyId]) VALUES (6, 6, 4)
SET IDENTITY_INSERT [dbo].[temp] OFF
/****** Object:  Table [dbo].[PolicyDetails]    Script Date: 05/11/2013 12:02:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PolicyDetails](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[CustomerId] [int] NULL,
	[PolicyId] [int] NULL,
	[CoverageId] [int] NULL,
	[ContractId] [int] NULL,
 CONSTRAINT [PK_PolicyDetails] 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].[PolicyDetails] ON
INSERT [dbo].[PolicyDetails] ([id], [CustomerId], [PolicyId], [CoverageId], [ContractId]) VALUES (1, 1, 1, 80, 78)
INSERT [dbo].[PolicyDetails] ([id], [CustomerId], [PolicyId], [CoverageId], [ContractId]) VALUES (2, 2, 1, 45, 234)
INSERT [dbo].[PolicyDetails] ([id], [CustomerId], [PolicyId], [CoverageId], [ContractId]) VALUES (3, 1, 2, 234, 78)
INSERT [dbo].[PolicyDetails] ([id], [CustomerId], [PolicyId], [CoverageId], [ContractId]) VALUES (4, 3, 2, 4567, 890)
INSERT [dbo].[PolicyDetails] ([id], [CustomerId], [PolicyId], [CoverageId], [ContractId]) VALUES (5, 1, 3, 234, 890)
INSERT [dbo].[PolicyDetails] ([id], [CustomerId], [PolicyId], [CoverageId], [ContractId]) VALUES (6, 4, 3, 234, 890)
INSERT [dbo].[PolicyDetails] ([id], [CustomerId], [PolicyId], [CoverageId], [ContractId]) VALUES (7, 5, 1, 746, 78)
INSERT [dbo].[PolicyDetails] ([id], [CustomerId], [PolicyId], [CoverageId], [ContractId]) VALUES (8, 6, 4, 234, 78)
INSERT [dbo].[PolicyDetails] ([id], [CustomerId], [PolicyId], [CoverageId], [ContractId]) VALUES (9, 4, 3, 567, 890)
INSERT [dbo].[PolicyDetails] ([id], [CustomerId], [PolicyId], [CoverageId], [ContractId]) VALUES (10, 7, 5, 234, 78)
INSERT [dbo].[PolicyDetails] ([id], [CustomerId], [PolicyId], [CoverageId], [ContractId]) VALUES (11, 7, 5, 789, 34)
INSERT [dbo].[PolicyDetails] ([id], [CustomerId], [PolicyId], [CoverageId], [ContractId]) VALUES (12, NULL, 4, 6780, 323)
SET IDENTITY_INSERT [dbo].[PolicyDetails] OFF

Thanks

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-05-11 : 09:41:00

Hi,

There are some questions about the result rows.
Like :
why , for CustomerID=1 , are return only 2 rows
for CustomerID=7 , I have
7 5 234
7 5 789


select T.CustomerID, PD.PolicyID, PD.CoverageID
from temp as T
	 inner join PolicyDetails as PD on T.CustomerID=PD.CustomerID
where T.CustomerID in (select PD.CustomerID from PolicyDetails PD where PD.CoverageID=234)

order by T.CustomerID

Not the best solutions, but are some things unclear

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-05-11 : 10:08:49

Thanks, Only 2 results as I want to return the row in temp table and the row with coverage id 234 only from PolicyDetails. Other rows are not relevant.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-11 : 16:48:04

quote:
Originally posted by collie

Thanks, Only 2 results as I want to return the row in temp table and the row with coverage id 234 only from PolicyDetails. Other rows are not relevant.

There seems to be some inconsistencies in your sample data. For example, in the PolicyDetails, id=5 says customer 1 has policyId=3 with coverageid = 234. Yet, in temp table, id=4 says that customer 4 has policyid = 3. Can a policy id belong to multiple customers, and can there be orphaned policies in the policy details table?

You did a great job at providing the input data. Can you also do the same thing to show what the desired output is? You could create a table and put them into that table and post the DDL. Now it is not clear whether you want two rows (based on your last posting), or whether the result you posted in your original posting, or something else.

Anyway, here is my attempt - probably not what you are looking for


select * from temp;
select * from [PolicyDetails]


select * from temp t
INNER join PolicyDetails p on
	p.CustomerId = t.CustomerID and p.PolicyId = t.PolicyId
	and p.CoverageId = 234

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources