Check for ens fornavn+efternavn

...Glemte lige at sige at jeg selvfølgelig vil have alle navnene. Dvs.

person_firstname|person_lastname|person_id

Peter          |Hansen        |120
Peter          |Hansen        |985
Anders          |Klausen        |324
Anders          |Klausen        |521
Anders          |Klausen        |542

Sql'en jeg har prøvet med så sådan ud:

SELECT         person_firstname, person_lastname, person_id
FROM         tblperson
GROUP BY     person_firstname, person_lastname
HAVING         count(*)>1
ORDER BY     person_firstname, person_lastname

SELECT a.person_firstname, a.person_lastname, a.person_id, b.person_id
FROM tblperson a, tblperson b
WHERE a.person_firstname=b.person_firstname AND a.person_lastname=b.person_lastname AND a.person_id <> b.person_id

Det hedder en self join

SELECT     DISTINCT b.*, organisation_name, function_name
FROM     (
    SELECT        person_firstname, person_lastname, person_birth
    FROM         tblperson
    GROUP BY     person_firstname, person_lastname, person_birth
    HAVING     count(*)>1
    ORDER BY     person_firstname, person_lastname, person_birth
    ) a,
    tblperson b,
WHERE         a.person_firstname=b.person_firstname
AND         a.person_lastname=b.person_lastname
ORDER BY     person_firstname, person_lastname, person_middlename, person_birth

>>arne_v: Nej, den kunne ikke bruges. Jeg fandt dog ud af ovenstående

Lukker

????

Hos mig ser den da ud til at virke perfekt !

CREATE TABLE tblperson (
  person_id INTEGER NOT NULL,
  person_firstname VARCHAR(25),
  person_lastname VARCHAR(25),
  PRIMARY KEY (person_id)
);
INSERT INTO tblperson VALUES(1,'Anders','Andersen');
INSERT INTO tblperson VALUES(2,'Børge','Børgesen');
INSERT INTO tblperson VALUES(3,'Christian','Christiansen');
INSERT INTO tblperson VALUES(4,'Anders','And');
INSERT INTO tblperson VALUES(5,'Carl','Christiansen');
INSERT INTO tblperson VALUES(6,'Søren','Sørensen');
INSERT INTO tblperson VALUES(7,'Anders','Andersen');
INSERT INTO tblperson VALUES(8,'Børge','Børgesen');
INSERT INTO tblperson VALUES(9,'Carsten','Christiansen');
INSERT INTO tblperson VALUES(10,'Z','Zzzz');
SELECT a.person_firstname, a.person_lastname, a.person_id, b.person_id
FROM tblperson a, tblperson b
WHERE a.person_firstname=b.person_firstname AND a.person_lastname=b.person_lastname AND a.person_id <> b.person_id;
DROP TABLE tblperson;

Output:

person_firstname        person_lastname person_id      person_id
Anders  Andersen        7      1
Børge  Børgesen        8      2
Anders  Andersen        1      7
Børge  Børgesen        2      8