Is there a way to Match and Unmatch in Same Query? 1

[BV874]

Hello,
I have a table that contains EMAIL, CLIENTID, DATE, USERNAME. Some of the EMAIL addresses are the same but with different CLIENTIDs. Most are in the table multiple times with different dates. I need to run a query that tells me what lines of data contain the same EMAIL address when the CLIENTID is different.

Is it possible to run a matched and unmatched query in the same query? I have tried it separately and it does not work with my data.

Thank you.

 

[PWise]

Try
Select EMAIL, CLIENTID, DATE, USERNAME
From Tablename
iner join(
Select Email,count(CLIENTID) as Co
From Tablename)Dups
on Dups.email= TablenameEmail
Group by Email

 

[BV874]

Thank you pwise, but I am not very good with sequel. I could not get this to run.

 

[SkipVought]

Inner has TWO n's

Also include spaces before and after the ().

Select Tablename.EMAIL, CLIENTID, DATE, USERNAME
From Tablename
inner join (
Select Email,count(CLIENTID) as Co
From Tablename )Dups
on Dups.email= Tablename.Email
Group by Tablename.Email

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Golom]

Yes ... but you're not doing anything with the count. It doesn't appear in the Select clause.

If you are Grouping By Tablename.Email then you will need some sort of aggregate function (e.g. First, Last, Min, Max) for the other fields in the Select.

Also, the Sub-Query needs to Group By EMail if you are going to use an aggregate function like Count.

 

[PWise]

Sorry Sb

Select Tablename.EMAIL, CLIENTID, DATE, USERNAME
From Tablename
inner join (
Select Email,count(CLIENTID) as Co
From Tablename 
Group by Tablename.Email
having count(CLIENTID)>1)Dups
on Dups.email= Tablename.Email

 

[BV874]

Thanks PWISE, GOLOM, and SKIPVOUGHT!