I have a table with a field for a member number. The table can contain records with duplicate values for this field and does.
I have a SQL query that will obtain any duplicate values for this field, however, I would now like to find records where there is another record with the same member number but only the first 8 digits out of the 9 that there are.
For example, I would like to return the below when the query is run;
MEMBNO 123456789 123456782
At the moment, my query only returns;
where there are two or more records with this exact number.
Current query is;
SELECT basic.MEMBNO FROM basic GROUP BY basic.MEMBNO HAVING COUNT(basic.MEMBNO) >1 ORDER BY basic.MEMBNO
A query I thought might work was;
SELECT basic.MEMBNO FROM basic GROUP BY basic.MEMBNO HAVING COUNT(LEFT(basic.MEMBNO,8)) >1 ORDER BY basic.MEMBNO
I'm faily new to SQL and databases in general, so apologies if I haven't explained myself that well. Does it require the LIKE operator somewhere?
I'm using Microsoft SQL Server Report Builder 3.0.