I have a SQL query I am trying to write but haven't been able to come up with the solution.
2 entries in my table that are related, are related through the first 3 characters of the ID. When a new item is added that needs to be related to a certain entry, the first 3 characters are added, and then the 2nd two are incremented by one to create the new ID. When a completely sepere entry is needed a unique 3 digit string is used and starts the second two chars with "00". There may be some that have large gaps in the last 2 columns because of deleted data (I don't want these in my query)
What I would like to do is get only the rows where there is another row with the same first 3 characters and 1 less from the count of the last 2.
Table Example:
ID | Date
====== ========
11100 | 07/12
22211 | 07/13
12300 | 07/14
11101 | 07/14
11400 | 07/16
22212 | 07/16
The Query should only return these elements because there exsists another entry with the same first 3 chars and one less from the last 2 chars .
ID | Date
====== ========
11101 | 07/14
22212 | 07/16
Looks like a simple JOIN will do it;
SELECT a.*
FROM Table1 a
JOIN Table1 b
ON a.id/100 = b.id/100
AND a.id = b.id + 1
You can also write it as an EXISTS query;
SELECT a.*
FROM Table1 a
WHERE EXISTS (
SELECT 1 FROM Table1 b WHERE b.id = a.id-1
AND a.id/100 = b.id/100
)