Comparing two fields from the same table

[Tommy.b]

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?

[rch1231]

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.

[Tommy.b]

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,'[e-mail]')
INSERT INTO test1
VALUES (2,'[e-mail]')
INSERT INTO test1
VALUES (3,'[e-mail]')
INSERT INTO test1
VALUES (4,'[e-mail]')

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

[Tommy.b]

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

[sankarssn]

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