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)
 self join more than one table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

collie
Constraint Violating Yak Guru

399 Posts

Posted - 04/16/2013 :  15:06:59  Show Profile  Reply with Quote
Hi,

I have 2 tables

CREATE TABLE [dbo].[Policy](
	[PolicyID] [int] IDENTITY(1,1) NOT NULL,
	        CompanyID INT
	[StartDate] datetime
	 
 )
 
 
  
CREATE TABLE [dbo].[PolicyDetails](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[PolicyID] INT NULL,
[CustomerID] INT NULL

	
	 
 )


I need to return all customers that have policies in both companyids 45 and 87. There are many companies but only 45 and 87 are relevant. If a customer has a policy under only one company it's not relevant.

How would I do that?

Thanks

James K
Flowing Fount of Yak Knowledge

3638 Posts

Posted - 04/16/2013 :  15:52:28  Show Profile  Reply with Quote
SELECT 
	Customer
FROM
	PolicyDetails
WHERE
	PolicyId IN (45,87)
GROUP BY
	Customer
HAVING
	COUNT(DISTINCT PolicyId) = 2
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/17/2013 :  00:49:01  Show Profile  Reply with Quote
for getting customer details you might have to join to customer reference table on related column (CustomerID). the given query will give you just ids of customers having those two policies.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

collie
Constraint Violating Yak Guru

399 Posts

Posted - 04/17/2013 :  17:18:51  Show Profile  Reply with Quote
Hi,

I have another question please. I have 2 tables.
Policy table with fields id , companyid, agentid
PolicyData table with fields id, policyid,customerid,rolecode.

I need to find customers that have more than one policy under the same companyid and agentid but that have different rolecodes.

Is this correct?
Select * from policy p1 inner join policy p2 on p1.companyid=p2.companyid and p1.agentid=p2.agentid
inner join policydata pd1 on pd1.policyid=p1.id
inner join policydata pd1 on pd2.policyid=p2.id and pd1.customerid=pd2.customerid
where pd2.rolecode<>pd1.rolecode
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3638 Posts

Posted - 04/17/2013 :  18:01:30  Show Profile  Reply with Quote
I think it should be more like this:
SELECT 
	pd.CustomerId,
	p.companyid,
	p.agentid
FROM
	PolicyData pd
	INNER JOIN Policy p ON
		p.id = pd.policyid
GROUP BY
	pd.CustomerId,
	p.companyid,
	p.agentid
HAVING
	COUNT(DISTINCT pd.rolecode) > 1
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.04 seconds. Powered By: Snitz Forums 2000