SQL Comparing Information in Tables

Question

I want to know how I would compare two pieces of information such as say I have two columns in my query: surname and forename. How could I check to see whether the forename exists in the surname column and visa versa?

So far I've created two temporary tables, one which selects just the surname, and one which selects all the other information. I was going to compare them by doing a join but im having no luck.

Welcome to Stack Overflow! We encourage you to [research your questions](http://stackoverflow.com/questions/how-to-ask). If you've [tried something already](http://whathaveyoutried.com/), please add it to the question - if not, research and attempt your question first, and then come back. — , Nov 07 '12 at 12:19

score 0 · Answer 1 · answered Nov 07 '12 at 12:03

Something like:

Select u1.Surname, u1.Forename,
    (select count(0) from users u2 where u1.surname = u2.forename) as CountWhereForenameEqualsMySurname,
     (select count(0) from users u2 where u1.forename = u2.surname ) as CountWhereSurnameEqualsMyForename
    From users u1

score 0 · Answer 2 · answered Nov 07 '12 at 12:41

Without knowing the structure of your tables it is a little difficult to write a query. But I would do something like this; join the table to itself based on the surname being equal to the forename.

SELECT
    t1.forename as foreName1,
    t1.surname as surName1,
    t2.forename as foreName2,
    t2.surname as surName2
FROM
    tableName as t1 INNER JOIN tablename as t2
        ON
            t1.surname = t2.forename

SQL Comparing Information in Tables

2 Answers2