QUERY to return data from primary table by checking the criteria in mapping/2nd table.

[pedie]

Hi Everyone!

I'm trying to create a QUERY to return data from primary table by checking the criteria in mapping/2nd table.
I tried using

SELECT Table1.ID, Table1.users, Table1.mydate, Table1.ContactNo
FROM Table2 INNER JOIN Table1 ON (Table2.mydate = Table1.mydate) AND (Table2.users = Table1.users);

but it returns all matching records from both tables which I do not want.
What I want is to return data from table2 only if mydate & users matches in both the tables.
In the same time I want to get contact info of user from table1 which is not available in table2.

Excel Workbook
	F	G	H	I	J	K	L	M
1	tb2				table1
2	ID	users	mydate		ID	users	mydate	ContactNo
3	1	user1	1-Jan-13		6	user1	1-Jan-13	123-123-45
4	2	user2	1-Jan-13		1	user1	1-Jan-13	123-123-45
5	3	user3	1-Jan-13		7	user2	1-Jan-13	123-123-47
6	4	user4	1-Jan-13		2	user2	1-Jan-13	123-123-47
7	5	user5	1-Jan-13		8	user3	1-Jan-13	123-123-52
8	6	user5	1-Jan-13		3	user3	1-Jan-13	123-123-52
9	7	user5	1-Jan-13		9	user4	1-Jan-13	123-123-55
10	8	user6	1-Jan-13		4	user4	1-Jan-13	123-123-55
11					10	user5	1-Jan-13	123-123-59
12					5	user5	1-Jan-13	123-123-59
13					11	user6	1-Jan-13	123-123-90
Sheet1

Table Last Updated 5/7/2013 11:53:25 PM

Excel Workbook
	O	P	Q	R
1	Expected result
2	ID	users	mydate	ContactNo
3	1	user1	1-Jan-13	123-123-45
4	2	user2	1-Jan-13	123-123-47
5	3	user3	1-Jan-13	123-123-52
6	4	user4	1-Jan-13	123-123-55
7	5	user5	1-Jan-13	123-123-59
8	6	user5	1-Jan-13	123-123-59
9	7	user5	1-Jan-13	123-123-59
10	8	user6	1-Jan-13	123-123-90
Sheet1

Table Last Updated 5/7/2013 11:53:50 PM

Excel Workbook
	A	B	C	D
1	Query result
2	ID	users	mydate	ContactNo
3	6	user1	1-Jan-13	123-123-45
4	1	user1	1-Jan-13	123-123-45
5	7	user2	1-Jan-13	123-123-47
6	2	user2	1-Jan-13	123-123-47
7	8	user3	1-Jan-13	123-123-52
8	3	user3	1-Jan-13	123-123-52
9	9	user4	1-Jan-13	123-123-55
10	4	user4	1-Jan-13	123-123-55
11	10	user5	1-Jan-13	123-123-59
12	5	user5	1-Jan-13	123-123-59
13	10	user5	1-Jan-13	123-123-59
14	5	user5	1-Jan-13	123-123-59
15	10	user5	1-Jan-13	123-123-59
16	5	user5	1-Jan-13	123-123-59
17	11	user6	1-Jan-13	123-123-90
Sheet1

Table Last Updated 5/7/2013 11:54:01 PM

 

[xenou]

This isn't clear. What are the ID's for? Why are the records repeating users/dates in table2 and users/dates/contacts in table1? What information do you need from this query?
ξ

 

[pedie]

Hi Xen, thanks for looking into this. The ID field is like a sort of serial #, please ignore it.
Both tables can have same user multiple times. I want return table2 user record information if user exist in table1. and i want the contact information to come from table2.

 

[xenou]

Do the dates matter? If not this might work (try and see):

SELECT Table2.ID, Table2.users, Table2.mydate, Table2.ContactNo
FROM Table2 WHERE Table2.Users IN (SELECT DISTINCT Table1.users FROM Table1);

If the dates do matter, then maybe:

SELECT Table2.ID, Table2.users, Table2.mydate, Table2.ContactNo
FROM Table2 
    INNER JOIN 
         (
         SELECT DISTINCT Table1.users, Table1.myDate 
         FROM Table1
         )
         AS TSub
ON Table2.users = TSub.users AND Table2.mydate = TSUB.myDate
;

 

[pedie]

Xen, this works perfect! thank you very much.