quote:

---

Originally posted by lazerath

Shows you all members from z that aren't in y:[CODE]SELECT
....WHERE y.MemberID
IS NULL;[/CODE]

Shows you all members in either y or z that don't exist in the other one:[CODE]SELECT ..........WHERE y.MemberID
IS NULL
OR z.MemberID
IS NULL;[/CODE]

---

quote:

---

Originally posted by Seventhnight

Are you trying to find the 100 people?? or the matching people?
I would be the reason you are getting 8034 is that you have multiple records the match on the join criteria. So 1 record in z may match 2 records y (so you would have 2 records for that case).

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

---

quote:

---

Originally posted by lazerath

Is COBID a foreign key to a Coordination of Benefits record?

---

quote:

---

Originally posted by lazerath

Xerxes,

I am deeply involved in the insurance world, so I feel your pain dealing with the data involved. Most places violate every known best practice there is concerning relational data.

As far as those queries are concerned, I am confused. Did you copy my queries exactly or did you modify them? They should work perfectly.

---

SELECT 		COUNT(*)
AS		UniqueMemberCount,

		SUM
		(
		CASE
		WHEN		z.MemberID
		IS		NOT NULL
		AND		y.MemberID
		IS		NOT NULL
		THEN		1
		ELSE		0
		END
		)
AS 		MatchingRecords,

		SUM
		(
		CASE
		WHEN		z.MemberID
		IS		NULL
		THEN		1
		ELSE		0
		END
		)
AS		OnlyInTableY,

		SUM
		(
		CASE
		WHEN		y.MemberID
		IS		NULL
		THEN		1
		ELSE		0
		END
		)
AS		OnlyInTableZ

FROM 		(
		SELECT	DISTINCT	tz.MemberID,
					tz.COBID
		FROM			Fowler.dbo.z_COBMembr
					tz
		)
		z

FULL JOIN	(
		SELECT	DISTINCT	ty.MemberID,
					ty.COBID
		FROM			Fowler.dbo.y_COBMembr
					ty
		)
		y

ON 		(
		z.MemberID
=		y.MemberID
		)
AND		(
		z.COBID
=		y.COBID
		);

quote:

---

Originally posted by Xerxes


Thanks, but I'm the medical bureaucracy field. I did run both sets in SQA , but I got 0 & 4. Would you recommend pks or fks?

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

---

YES!

quote:

---

Would you recommend pks or fks

---

quote:

---

Originally posted by tduggan

quote:

---

Would you recommend pks or fks

---

Please tell me that this isn't a production database.

So how are you handling referential integrity? You are going to say the application handles it, right? Do you have a DBA there to slap you for me?

Tara

---

select 	z.MemberName, z.MemberID, z.COBName, z.StartDate, z.TermDate, z.COBID, z.MedPrimary,
from 	[Fowler].[dbo].[z_COBMembr] z
group by z.MemberName, z.MemberID, z.COBName, z.StartDate, z.TermDate, z.COBID, z.MedPrimary
having count(*)>1

select 	y.MemberName, y.MemberID, y.COBName, y.StartDate, y.TermDate, y.COBID, y.MedPrimary
from 	[Fowler].[dbo].[y_COBMembr] y 
group by y.MemberName, y.MemberID, y.COBName, y.StartDate, y.TermDate, y.COBID, y.MedPrimary
having count(*)>1

quote:

---

Originally posted by spirit1

xerx frist do this:
it will give you the records that are duplicated.

select 	z.MemberName, z.MemberID, z.COBName, z.StartDate, z.TermDate, z.COBID, z.MedPrimary,
from 	[Fowler].[dbo].[z_COBMembr] z
group by z.MemberName, z.MemberID, z.COBName, z.StartDate, z.TermDate, z.COBID, z.MedPrimary
having count(*)>1

select 	y.MemberName, y.MemberID, y.COBName, y.StartDate, y.TermDate, y.COBID, y.MedPrimary
from 	[Fowler].[dbo].[y_COBMembr] y 
group by y.MemberName, y.MemberID, y.COBName, y.StartDate, y.TermDate, y.COBID, y.MedPrimary
having count(*)>1

then go on from there...

Go with the flow & have fun! Else fight the flow

---

quote:

---

Originally posted by lazerath

quote:

---

Originally posted by Xerxes


Thanks, but I'm the medical bureaucracy field. I did run both sets in SQA , but I got 0 & 4. Would you recommend pks or fks?

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

---

Its all different ends of the same industry, really. :-)

---