SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 get results from 2 tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

collie
Constraint Violating Yak Guru

399 Posts

Posted - 05/11/2013 :  05:02:44  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

Romania
419 Posts

Posted - 05/11/2013 :  09:41:00  Show Profile  Reply with Quote
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

Edited by - stepson on 05/11/2013 09:41:32
Go to Top of Page

collie
Constraint Violating Yak Guru

399 Posts

Posted - 05/11/2013 :  10:08:49  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 05/11/2013 :  16:48:04  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000