SQL Problem 1

[Kinl]

I know this should probably go in the ANSI_SQL area, but it seems that not that many people look at that place. Maybe someone here can help me. (I am using this SQL in the asp page though!!.. does that count!?!.. well lets hope so.)

I have this form that people enter information into for a contest. Unfortunately, the way the form was built back in the day, was so that you can only enter the contest once, (this is perfect), EXCEPT, if you change your name you can enter more than once. SUch as, Bill, to Will. Or Bill to Billy. The asp checks the first, last, address, and zip code of the information entered.

Heres the problem with the SQL.I need to create a query that will show which persons (based on there last name usually) have entered more than once. I tried this: ... (below) ... but it states I cant return multiple sets of data.

How can I find out, through a query, how many contestants have entered the contest more than once. Basically, if your last name and zipcode, address is in there more than once, will a query be able to pull this. I dont want to have to manually know there name. I would like to run the query and it return the last names of the people in the database that are in it more than once.
-------------------------------
SQL:

SELECT fname, lname, address
FROM tbl_table
WHERE ((SELECT lname FROM tbl_table)) = (SELECT lname FROM tbl_table))


Thanx,

donn

 

[link9]

SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

is a good template for finding dupes, and here's one tailored to your situation:

SELECT lName, zipCode, address, count(*)
FROM tableName
GROUP BY lName, zipCode, address
HAVING count(*) > 1

Is that what you're looking for?

Paul Prewett

 

[Kinl]

Thanx Paul,

I've seen this done before, but its monday morning, and my brain is still not working just yet. (Need more caffeine!)..

This is exactly what I was looking for! Thanx man!
You got my vote!

Donn