Finding duplicates?

[nosrepmynona]

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]

create a query linking the two tables on userid and criteria where number between startnum and endnum

 

[MajP]

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;

 

[nosrepmynona]

Thank you, I’ll try it out.