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]
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -