MySql Same table join problem

[llapgoch]

Hello there, thank you for your help in advance.

I've got table containing student data. The data is exported by a program called SIMS and i've written a script to import the text file.

The users' usernames are created by concatenating their DOB and initial of first and last name for ease of use by the students. Obviously this is not a unique method and some students' usernames do clash, whereby the script adds an addittional integer to the end of the conflicting username.

It's all fine up to this point...

Schools are going to want to see a list of usernames which clash and are not normal. I need to write a query which selects usernames from the table whose 8 left characters are the same as any other usernames eight left characters.

Obviously;
select uname from students where left(uname,8)=left(uname,8)

will return all records as records will be matched to themselves. Is there a way to only return the record if it matches two or more other records?

Thank you in advanve,
llapgoch

[tomhath]

select s1.uname, s2.uname
from students s1
inner join students s2
on left(s1.uname,8)=left(s2.uname,8)
where s1.id <> s2.id

[llapgoch]

You rule, tomhath. Does the s1 and s2 alias the table then? I was just messing around with aliases, but I didn't fully finish it.

Thank you very much!

[tomhath]

Yes, the alias gives you a way of selecting two different rows from the same table.

Looking at it again, I'd write it this way to eliminate duplicates:

select distinct s1.uname
from students s1
inner join students s2
on left(s1.uname,8)=left(s2.uname,8)
where s1.id <> s2.id
order by s1.uname

[llapgoch]

That's great, thanks again mate. I think I now understand aliases, wahoo!

😃