compare values of same column on same table

Question

This has got to be a simple question, just brain-dumbing right now...

I've got one table, called 'foo'. It's got two columns, 'id' and 'username'.

The id is unique, but some of the usernames reference the same user; just one has a prefix on the username of 'xx_'.

ex:
ID      USERNAME
1       bob
2       sam
3       xx_bob

How can I figure out which of the users have a counterpart with the 'xx_' prefix? And then which ones do not?

Answer 1

select * from foo where username 
 IN (select replace(username, 'xx_', '') from foo where username like 'xx_%')

What this does is compares the entire table against a sub list which is generated by the sub-query after the IN verb.

To do the opposite you can simply use a NOT IN in place of the IN.

NOTE: This is a t-sql (MS SQL 2005) query, should be similar in MySQL

Answer 2

This will give you the id's of both rows:

select * from foo a1 join foo a2 on (a2.username=concat('xx_',a1.username));

Answer 3

If you want every rows that isn't a duplicate, with the duplicate_id:

SELECT foo.*, f2.id AS duplicate_id FORM foo
LEFT OUTER JOIN foo AS f2 ON ( f2.username = concat( 'xx_', foo.username ) )
WHERE foo.id NOT LIKE 'xx_%'