Need help combining views; DDL included

I've solved this issue by creating 3 views but I'd rather do it in 1 SELECT
if possible.
Given my data I want to select duplicate securities based on the cusip field
in the Securities table where the cusip does not exist in the Positions
table. To rephrase I want duplicate securities that are not held.
Given my sample data I want to return one record with the cusip value 'E'
Thanks to anyone who could help.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Positions_Securities]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Positions] DROP CONSTRAINT FK_Positions_Securities
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Positions]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Securities]
GO
CREATE TABLE [dbo].[Positions] (
[AccountID] [int] NOT NULL ,
[SecurityID] [int] NOT NULL ,
[Quantity] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Securities] (
[SecurityID] [int] NOT NULL ,
[CUSIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Positions] WITH NOCHECK ADD
CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
(
[AccountID],
[SecurityID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Securities] WITH NOCHECK ADD
CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
(
[SecurityID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Positions] ADD
CONSTRAINT [FK_Positions_Securities] FOREIGN KEY
(
[SecurityID]
) REFERENCES [dbo].[Securities] (
[SecurityID]
)
GO
INSERT INTO Securities (SecurityID,CUSIP) VALUES (1,'A')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (2,'A')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (3,'B')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (4,'C')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (5,'D')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (6,'E')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (7,'E')
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,1,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,2,15)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,1,20)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (5,4,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,3,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,3,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (3,5,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,2,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,5,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,5,10)Try This:
Select CusIP, Count(*)
From Securities
Where SecurityID Not In
(Select SecurityID
From Positions)
Group By CusIP
Having Count(*) > 1
"Terri" wrote:

> I've solved this issue by creating 3 views but I'd rather do it in 1 SELEC
T
> if possible.
> Given my data I want to select duplicate securities based on the cusip fie
ld
> in the Securities table where the cusip does not exist in the Positions
> table. To rephrase I want duplicate securities that are not held.
> Given my sample data I want to return one record with the cusip value 'E'
> Thanks to anyone who could help.
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_Positions_Securities]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[Positions] DROP CONSTRAINT FK_Positions_Securities
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Positions]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Securities]
> GO
> CREATE TABLE [dbo].[Positions] (
> [AccountID] [int] NOT NULL ,
> [SecurityID] [int] NOT NULL ,
> [Quantity] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Securities] (
> [SecurityID] [int] NOT NULL ,
> [CUSIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] WITH NOCHECK ADD
> CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
> (
> [AccountID],
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Securities] WITH NOCHECK ADD
> CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
> (
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] ADD
> CONSTRAINT [FK_Positions_Securities] FOREIGN KEY
> (
> [SecurityID]
> ) REFERENCES [dbo].[Securities] (
> [SecurityID]
> )
> GO
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (1,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (2,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (3,'B')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (4,'C')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (5,'D')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (6,'E')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (7,'E')
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,1,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,2,15)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,1,20)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (5,4,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (3,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,2,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,5,10)
>
>|||What sulld happen in case that you add this to your sample data:
INSERT INTO Securities (SecurityID,CUSIP) VALUES (8,'E')
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,8,10)
Would "E" still satisfy your request?
"Terri" wrote:

> I've solved this issue by creating 3 views but I'd rather do it in 1 SELEC
T
> if possible.
> Given my data I want to select duplicate securities based on the cusip fie
ld
> in the Securities table where the cusip does not exist in the Positions
> table. To rephrase I want duplicate securities that are not held.
> Given my sample data I want to return one record with the cusip value 'E'
> Thanks to anyone who could help.
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_Positions_Securities]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[Positions] DROP CONSTRAINT FK_Positions_Securities
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Positions]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Securities]
> GO
> CREATE TABLE [dbo].[Positions] (
> [AccountID] [int] NOT NULL ,
> [SecurityID] [int] NOT NULL ,
> [Quantity] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Securities] (
> [SecurityID] [int] NOT NULL ,
> [CUSIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] WITH NOCHECK ADD
> CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
> (
> [AccountID],
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Securities] WITH NOCHECK ADD
> CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
> (
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] ADD
> CONSTRAINT [FK_Positions_Securities] FOREIGN KEY
> (
> [SecurityID]
> ) REFERENCES [dbo].[Securities] (
> [SecurityID]
> )
> GO
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (1,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (2,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (3,'B')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (4,'C')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (5,'D')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (6,'E')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (7,'E')
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,1,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,2,15)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,1,20)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (5,4,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (3,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,2,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,5,10)
>
>|||Anyway,
If your asnwer is Yes:
Solution 1:
select
sec.CUSIP
from Positions pos right outer join Securities sec
on pos.SecurityID=sec.SecurityID
where pos.AccountID is null
group by sec.CUSIP
having count(sec.CUSIP)>1
Solution 2:
select
sec.CUSIP
from Securities sec
where sec.SecurityID not in
(
select pos.SecurityID from Positions pos
)
group by sec.CUSIP
having count(sec.CUSIP)>1
If your answer is NO:
select CUSIP
from Securities
where CUSIP not in
(
select sec.CUSIP
from Positions pos join Securities sec
on pos.SecurityID=sec.SecurityID
)
group by CUSIP
having count(CUSIP)>1
"Terri" wrote:

> I've solved this issue by creating 3 views but I'd rather do it in 1 SELEC
T
> if possible.
> Given my data I want to select duplicate securities based on the cusip fie
ld
> in the Securities table where the cusip does not exist in the Positions
> table. To rephrase I want duplicate securities that are not held.
> Given my sample data I want to return one record with the cusip value 'E'
> Thanks to anyone who could help.
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_Positions_Securities]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[Positions] DROP CONSTRAINT FK_Positions_Securities
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Positions]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Securities]
> GO
> CREATE TABLE [dbo].[Positions] (
> [AccountID] [int] NOT NULL ,
> [SecurityID] [int] NOT NULL ,
> [Quantity] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Securities] (
> [SecurityID] [int] NOT NULL ,
> [CUSIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] WITH NOCHECK ADD
> CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
> (
> [AccountID],
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Securities] WITH NOCHECK ADD
> CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
> (
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] ADD
> CONSTRAINT [FK_Positions_Securities] FOREIGN KEY
> (
> [SecurityID]
> ) REFERENCES [dbo].[Securities] (
> [SecurityID]
> )
> GO
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (1,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (2,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (3,'B')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (4,'C')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (5,'D')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (6,'E')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (7,'E')
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,1,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,2,15)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,1,20)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (5,4,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (3,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,2,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,5,10)
>
>|||Terri
See Itzik Ben-Gan's script about duplicates
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Terri" <terri@.cybernets.com> wrote in message
news:d3f1h9$ocg$1@.reader2.nmix.net...
> I've solved this issue by creating 3 views but I'd rather do it in 1
SELECT
> if possible.
> Given my data I want to select duplicate securities based on the cusip
field
> in the Securities table where the cusip does not exist in the Positions
> table. To rephrase I want duplicate securities that are not held.
> Given my sample data I want to return one record with the cusip value 'E'
> Thanks to anyone who could help.
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_Positions_Securities]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[Positions] DROP CONSTRAINT FK_Positions_Securities
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> drop table [dbo].[Positions]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> drop table [dbo].[Securities]
> GO
> CREATE TABLE [dbo].[Positions] (
> [AccountID] [int] NOT NULL ,
> [SecurityID] [int] NOT NULL ,
> [Quantity] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Securities] (
> [SecurityID] [int] NOT NULL ,
> [CUSIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] WITH NOCHECK ADD
> CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
> (
> [AccountID],
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Securities] WITH NOCHECK ADD
> CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
> (
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] ADD
> CONSTRAINT [FK_Positions_Securities] FOREIGN KEY
> (
> [SecurityID]
> ) REFERENCES [dbo].[Securities] (
> [SecurityID]
> )
> GO
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (1,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (2,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (3,'B')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (4,'C')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (5,'D')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (6,'E')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (7,'E')
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,1,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,2,15)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,1,20)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (5,4,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (3,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,2,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,5,10)
>

Need assistance with Duplicate Select Statment

Any help would be appreciated here. My question is two part:

1.) Select duplicates that match any of a number of columns for example...

The email is the same OR

The homephone is the same OR

The mobilephone is the same OR

The address1 is the same

The uniqueID is ConsIntID

My Select Statement: (Which does not work)

SELECT CONSINTID, FIRSTNAME, LASTNAME, EMAIL1, ADDRESS1, HOMEPHONE, MOBILEPHONE, CREATEDATE, USERIDS

FROM CONSULTANTS

WHERE CONSINTID IN (SELECT CONSINTID

FROM CONSULTANTS

HAVING COUNT(HOMEPHONE) > 1 OR COUNT(MOBILEPHONE) >1 OR etc.... )

-

Once the Select Duplicates Statement is corrected I will insert those into a ConsultantsDupe Table

The Second Question is: If I have more then one duplicate my stored_procedure fails

Here is my cursor and logic. The problem is getting multiple results in the subquery and I don't know the best way to correct this.

DECLARE DUPES CURSOR FOR

SELECT CONSINTID, FIRSTNAME, LASTNAME, EMAIL1, ADDRESS1, HOMEPHONE, MOBILEPHONE, CREATEDATE, USERIDS

FROM CONSULTANTS_DUPS

ORDER BY CREATEDATE DESC

OPEN DUPES

FETCH NEXT FROM DUPES

INTO @.CONSINTID, @.FIRSTNAME, @.LASTNAME, @.EMAIL1, @.ADDRESS1, @.HOMEPHONE, @.MOBILEPHONE, @.CREATEdATE, @.USERIDS

WHILE @.@.FETCH_STATUS = 0

BEGIN

SET @.FOUNDCONSINTID = (SELECT DISTINCT CONSINTID FROM CONSULTANTS_DUPS

WHERE CONSINTID <> @.CONSINTID AND HOMEPHONE = @.HOMEPHONE "NEED TO ADD COMPARISON FOR MOBILEPHONE, EMAIL ADDRESS HERE") <== This can provide more then one result which messes me up.

I'll take a couple shots at this. Hopefully somebody can post something even better. You'll have to forgive me. Without any sample tables I had to "shoot from the hip" and write it without any syntax checking.

SELECT * FROM Consultants C

WHERE EXISTS (SELECT * FROM Consultants CPhone

WHERE (C.HomePhone = CPhone.HomePhone

OR C.HomePhone = CPhone.CellPhone

OR C.CellPhone = CPhone.HomePhone

OR C.CellPhone = CPhone.CellPhone)

AND C.CONSINTID <> CPhone.CONSINTID

)

OR EXISTS (SELECT * FROM Consultants CEmail

WHERE C.EMAIL1 = CEmail.EMAIL1

AND C.CONSINTID <> EMAIL1.CONSINTID)

OR EXISTS ...

Here's another shot:

SELECT * FROM Consultants

WHERE HomePhone IN (SELECT HomePhone

FROM Consultants

WHERE HomePhone IS NOT NULL

GROUP BY HomePhone

HAVING COUNT(*) > 1)

OR CellPhone IN (SELECT CellPhone

FROM Consultants

WHERE CellPhone IS NOT NULL

GROUP BY CellPhone

HAVING COUNT(*) > 1)

OR Email1 IN (SELECT Email1

FROM Consultants

WHERE Email1 IS NOT NULL

GROUP BY Email1

HAVING COUNT(*) > 1)

This method suffers from the fact that you can't check HomePhone and CellPhone at the same time (with UNION ALL) because of a contact has the same phone number listed as home and cell then it could show up as a duplicate.

Here's where you might take the previous query as a natural transition to cross-check home phone against cell phone:

SELECT * FROM Consultants C

WHERE HomePhone IN (SELECT HomePhone

FROM (SELECT CONSINTID, HomePhone

FROM Consultants UNION

SELECT CONSINTID, CellPhone

FROM Consultants) SubQ

WHERE HomePhone IS NOT NULL

GROUP BY HomePhone

HAVING COUNT(*) > 1)

OR CellPhone IN (SELECT HomePhone

FROM (SELECT CONSINTID, HomePhone

FROM Consultants UNION

SELECT CONSINTID, CellPhone

FROM Consultants) SubQ

WHERE HomePhone IS NOT NULL

GROUP BY HomePhone

HAVING COUNT(*) > 1)

OR Email1 IN (SELECT Email1

FROM Consultants

WHERE Email1 IS NOT NULL

GROUP BY Email1

HAVING COUNT(*) > 1)

You may also be better off with multiple SELECT statements as it's hard to tell the cause of your duplicate in a query like this. Keep in mind that all of these queries are very read-intensive as you're dealing with multiple scans across your table. You may benefit, instead, by placing your duplicates in a temporary table (or table variable) and then querying against the temporary table.

|||something like this:

select *
from consultants
where consintid in
(select consintid
from consultants
group by email
having count(*)>1
union all
select consintid

from consultants

group by homephone

having count(*)>1

union all
select consintid

from consultants

group by cellphone

having count(*)>1

union all
select consintid

from consultants

group by address1

having count(*)>1
)|||

Jared,

Yours worked! This gives me what I need for the duplicate temp table and the assistance is greatly appreciated.

Any feedback on the second portion?

Need a way to exclude duplicate information

I've created a query:
SELECT DISTINCT co.CL_Desc,a.AN_Key,a.CL_Code,a.AT_Code,a.AN_Refer ence,a.AN_ToDo,a.An_Closed
,at2.CU_Value2,an1.CU_Value1,at3.CU_Value5 /* INNER JOIN FIELDS */
FROM clients co
LEFT OUTER JOIN Action a ON co.CL_Code = a.CL_Code /* Get actions */
LEFT OUTER JOIN ActionCustomTxt2 at2 ON a.AN_Key=at2.AN_Key /* Project checking */
LEFT OUTER JOIN ActionCustomNum1 an1 ON a.AN_Key=an1.AN_Key /* Date created */
LEFT OUTER JOIN AECTESUSER.ActionCustomTxt3 at3 ON a.AN_Key=at3.AN_Key /* Diagnostic levels */
WHERE AT_Code IN ('ENQUIRY','DIAGNOSTIC','ONWARD REFERRAL','CONSULTANCY/CONTRACT')
AND at2.CU_Value2='40201'
AND (an1.CU_Value1 BETWEEN 37851 AND 37857)
ORDER BY CL_Desc,a.AN_Reference

And it returns somthing like

CompanyA,Enquiry,04/09/2003
CompanyA,Contract,05/09/2003
ComanyB,Enquiry,04/09/2003

And so on
I know this is not true duplcates but for me its a problem. I only want the last record of each company to display being the most recent record. I thought I union query may have helped to solve this, but no luck has anyone got any ideas?What is your last record? The record with the highest ID per company? Select those ID's per company, and INNER JOIN the result with your original table!|||DB's general solution in code:
(I assumed that AN_Closed was the date value and that you wanted the latest one, rather than an ID value.) Because you are using OUTER joins, I think you need two levels of nesting to be sure you get all the data you want, but I couldn't test the code.

SELECT DISTINCT co.CL_Desc, a.AN_Key, a.CL_Code, a.AT_Code, a.AN_Reference, a.AN_ToDo, a.An_Closed, at2.CU_Value2, an1.CU_Value1, at3.CU_Value5 /* INNER JOIN FIELDS */
FROM clients co
LEFT OUTER JOIN /* Get last actions */
LEFT OUTER JOIN
(select An_Key, CL_Code, AT_Code, AN_Reference, AN_ToDo, AN_Closed
from Action
inner join (select CL_Code, max(AN_Closed) Last_AN_Closed from Action group by CL_Code) Last_Values
on Action.CL_Code = Last_Values.CL_Code and Action.AN_Closed = Last_Values.Last_An_Closed) a
ON co.CL_Code = a.CL_Code
LEFT OUTER JOIN ActionCustomTxt2 at2 ON a.AN_Key=at2.AN_Key /* Project checking */
LEFT OUTER JOIN ActionCustomNum1 an1 ON a.AN_Key=an1.AN_Key /* Date created */
LEFT OUTER JOIN AECTESUSER.ActionCustomTxt3 at3 ON a.AN_Key=at3.AN_Key /* Diagnostic levels */
WHERE AT_Code IN ('ENQUIRY','DIAGNOSTIC','ONWARD REFERRAL','CONSULTANCY/CONTRACT')
AND at2.CU_Value2='40201'
AND (an1.CU_Value1 BETWEEN 37851 AND 37857)
ORDER BY CL_Desc,a.AN_Reference

blindman