Hello,

I have a table with clients: id, name, email and the likes

I am looking to find all ID's where emails are the same, I tried the following

select db_email, db_medlem, count(*) from kunder group by db_email HAVING count(*) > 1;

however this generates an error due to not grouping on db_medlem as well

Column 'kunder.DB_MEDLEM' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Obviously I cannot group on db_medlem since I am only looking for dublicates in the email field.

Any ideas on this?

Re: Comparing two fields from the same table 80 80

You could use a sub-queries to generate the main query. My example is kind of an odd way to accomplish this but it should work.

Basically to get this working you run a selection to generate a list of the db_emails that have more than one record. Strip out just the db_email field and use it to go back through the table selecting the records where the email address is in the list.

First get the two main queries working.

Query 1: What do I want the output to look like

select db_email, db_medlem 
from kunder 
order by db_email

Query 2: Create a select to output db_email and count where count > 1

SELECT db_email, count(*) 
FROM kunder 
GROUP BY db_email 
HAVING count(*) > 1

Refine Query 2 to just give you the db_email field.

SELECT data1.db_email from ((SELECT db_email, count(*) 
FROM kunder 
GROUP BY db_email 
HAVING count(*) > 1) as data1)

Plug Query 2 into query1 as a list to select from.

select db_email, db_medlem 
from kunder 
where db_email in 
(SELECT data1.db_email from ((SELECT db_email, count(*) 
FROM kunder 
GROUP BY db_email 
HAVING count(*) > 1) as data1)
)
order by db_email

That is as close as I can get without the actual db to play with so you may have to do a little tweaking. I may be off with one of the () around data1. If you want send me the results or errors and I will debug it for you.

Re: Comparing two fields from the same table 80 80

Wow, thanks alot for the help so far, it makes it quite simple to follow what you are trying to do, and I get the general idea. That being said, I am clearly not good enough to be doing this.

SELECT db_email, db_medlem
         FROM kunder
         WHERE db_email IN
      (
			SELECT data1.db_email FROM ((SELECT db_email, count(*)
			FROM kunder
			GROUP BY db_email
			HAVING count(*) > 1) AS data1)
      )
      ORDER BY db_email

I get an error:
Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near ')'.

Having removed the ) after data1 on line 8, then i get an error for ) on line ), and then it definately does not work due to the order clause.

Executing just

SELECT db_email FROM (SELECT db_email, count(*)
      FROM kunder
      GROUP BY db_email
      HAVING count(*) > 1) as data1
gives
Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'data1'

which I dont quite follow either.

If you want something to test on.

CREATE TABLE test1
(
db_medlem char(50),
db_email varchar(255)
)

INSERT INTO test1
VALUES (1,'test@example.com')
INSERT INTO test1
VALUES (2,'test@example.com')
INSERT INTO test1
VALUES (3,'foo@example.com')
INSERT INTO test1
VALUES (4,'foo@example.com')

That creates a table that basically contains the same information that I have to work with, ideally the SQL command will result in a list of db_medlem, that have the same email.

Thanks again for your help

Re: Comparing two fields from the same table 80 80
SELECT db_email,db_medlem
FROM kunder
WHERE db_email IN (
 SELECT db_email
 FROM kunder
 GROUP BY db_email
 HAVING (COUNT(db_email) > 1 and db_email != '')
)

does the trick, thanks for the inspiration

Re: Comparing two fields from the same table 80 80

actually am having a single table with category_id and category_parent id
I want to pick the category_title that is a child category . that is for child category am giving parent id as the respacted parent autoincrement id. what i want is actually there are 3 categories.
category
sub category
child category.
category having parent id as 0
. i need to pick only by comparing two columns that is cat_id and cat_parentid and display the cat_title of cat_id. that is i need to display when all cat_id compared with all cat_parent id and which are not having the respected cat_id

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.