self join more than one table - 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)

self join more than one table

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-04-16 : 15:06:59

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-16 : 15:52:28

[code]SELECT
Customer
FROM
PolicyDetails
WHERE
PolicyId IN (45,87)
GROUP BY
Customer
HAVING
COUNT(DISTINCT PolicyId) = 2[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-17 : 00:49:01

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

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-04-17 : 17:18:51

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-17 : 18:01:30

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources