Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> Transact-SQL

Strange result from a simple JOIN

I am currently studying Transact SQL and playing around with queries from a sample database. Recently I created the following query.

USE MemtrackSQL

SELECT m1.MemberID, m1.Surname, m1.FirstName, m1.DateOfBirth

FROM tblMember m1JOIN tblMember m2

ON m1.FirstName= m2.FirstName

WHERE m1.MemberID<> m2.MemberID

This simple query is designed to show all members with the same first name as other members. The result I got shows duplicates of existing members an inconsistent number of times even though I specified not to show duplicates with WHERE m1.MemberID<> m2.MemberID

2 Scharenguivil Rodney 1958-06-24 00:00:00.000
2 Scharenguivil Rodney 1958-06-24 00:00:00.000
2 Scharenguivil Rodney 1958-06-24 00:00:00.000
5 O'Grady Patrick 1975-09-23 00:00:00.000
7 Greenfield Lynne 1955-07-26 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000

Any help in explaining where I have gone wrong here would be greatly appreciated.

Cheers

[2261 byte] By [Aden] at [2008-1-9]
«« Useful notes about Visual Studio Internet Plugin
»» Synchronous Pattern in MSRS
# 1

The where clause of your sql statement isn't/and should not eliminating duplicates in your example. To help you understand what the query is doing try adding m2.MemberID to the select list. If you want to remove duplicates for the current query you need to add a qroup by clause.

Code Snippet

SELECT m1.MemberID, m1.Surname, m1.FirstName, m1.DateOfBirth

FROM tblMember m1 JOIN tblMember m2

ON m1.FirstName = m2.FirstName

WHERE m1.MemberID <> m2.MemberID

group by m1.memberID, m1.Surname, m1.FirstName, m1.DateOfBirth

AnthonyMartin at 2007-10-3 > top of Msdn Tech,SQL Server,Transact-SQL...
# 2

Thanks heaps for the response and the suggestion that I add m2.MemberID, after doing so it became clear that what the query is doing is that it is inserting a record of each record for each instance of duplication. And of course adding the GROUP BY clause means that only one record is shown when there is a match.

Cheers

Aden at 2007-10-3 > top of Msdn Tech,SQL Server,Transact-SQL...
# 3

Here are two queries that I think will work for you:

select m1.MemberID, m1.Surname, m1.FirstName, m1.DateOfBirth

from tblMember as M1

where exists (

select * from tblMember as M2

where M2.FirstName = M1.FirstName

and M2.MemberID <> M1.MemberID

)

or

select MemberID, Surname, FirstName, DateOfBirth

from tblMember

where FirstName in (

select FirstName

from tblMember

group by FirstName

having count(distinct MemberID) > 1

)

Steve Kass

Drew University

http://www.stevekass.com

SteveKass at 2007-10-3 > top of Msdn Tech,SQL Server,Transact-SQL...
SQL Server
Transact-SQL

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified