Finding duplicates? (1 Viewer)

nosrepmynona

New member
Local time
Today, 08:58
Joined
Apr 25, 2020
Messages
6
Good evening!

I would be very thankful if someone can help me getting forward with a queries I can’t solve by myself. I’ve got two tables which has this structure:

TABLE 1
Column 1 contains numbers and column 2 contains userID's. For example:

Number , UserID
1 , A
2 , A
3 , B
4 , C
5 , D
6 , D
7 , A
8 , C
9 , D
10 , B

TABLE 2
Contains three columns; UserID's and one column for start number and one column for end number (interval separated in two columns). For example:

UserID , Start number , Ending number
A , 1 , 3
A , 5 , 6
B , 1 , 15
C , 1 , 1
D , 5 , 5
E , 1 , 20
F , 5 , 8


I would like to set up a queries who shows all numbers from TABLE 1 where UserID is identicaly in both TABLE 1 and TABLE 2 and number from TABLE 1 appears within the interval in TABLE 2. (finding duplicates)
In the example above the list should look like:

Number , UserID
1 , A
2 , A
3 , B
5 , D
10 , B

Is it possible to make a queries which can handle it?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:58
Joined
Feb 19, 2013
Messages
12,926
create a query linking the two tables on userid and criteria where number between startnum and endnum
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:58
Joined
May 21, 2018
Messages
4,763
Code:
SELECT tbl1.num,
       tbl1.userid
FROM   tbl1
       INNER JOIN tbl2
               ON tbl1.userid = tbl2.userid
WHERE  (( ( tbl1.num ) BETWEEN [tbl2].[startnumber] AND [tbl2].[endnumber] ))
ORDER  BY tbl1.num;
 

Users who are viewing this thread

Top Bottom