Inner Join. MS Access Query. How to return data from table1 only...

[pedie]

Hi Everyone!

How to return data from table1 only if table key matches table2 key.

I tried this but doesnt return me the data i want.
SELECT A.*
FROM tbA AS A INNER JOIN tbB AS B ON A.[ keyfld] = B.[keyfld];

Regards,
Pedie

 

[CJ_London]

How to return data from table1 only if table key matches table2 key

Why doesn't it return the data you want? It is constructed to return only those records where there is a match.

 

[pedie]

Hi CJ_London, thanks for quick reply.

I have two sample table

table1
ID users mydate
1 user1 1/1/2013
2 user2 1/1/2013
3 user3 1/1/2013
4 user4 1/1/2013
5 user5 1/1/2013
6 user1 1/1/2013
7 user2 1/1/2013
8 user3 1/1/2013
9 user4 1/1/2013
10 user5 1/1/2013

table2
ID users mydate
1 user1 1/1/2013
2 user2 1/1/2013
3 user3 1/1/2013
4 user4 1/1/2013
5 user5 1/1/2013



Current quiry returns
SELECT Table1.users, Table1.mydate
FROM Table2 INNER JOIN Table1 ON (Table2.mydate = Table1.mydate) AND (Table2.users = Table1.users);

ID users mydate
6 user1 1/1/2013
1 user1 1/1/2013
7 user2 1/1/2013
2 user2 1/1/2013
8 user3 1/1/2013
3 user3 1/1/2013
9 user4 1/1/2013
4 user4 1/1/2013
10 user5 1/1/2013
5 user5 1/1/2013

where as i want

ID users mydate
1 user1 1/1/2013
2 user2 1/1/2013
3 user3 1/1/2013
4 user4 1/1/2013
5 user5 1/1/2013



I want to compare 2 fields date and user between table1 and table2. if user and mydate matches return only records in table2 and not from table1.

 

[CJ_London]

Try

SELECT DISTINCT Table1.users, Table1.mydate

 

[pedie]

This sql is giving the desired result.
Is this query going to be slow with more data? Please advice.

SELECT Table2.users, Table2.mydate
FROM Table2
WHERE (((Table2.users) In (SELECT Table1.users FROM Table1 GROUP BY Table1.users, Table1.mydate HAVING (((Table1.mydate)=#1/1/2013#)))) AND ((Table2.mydate)=#1/1/2013#));

 

[CJ_London]

Personally I prefer DISTINCT but group by will work - in my experience GROUP BY can be slower but does depend on the query - make sure the User and date fields are indexed

 

[pedie]

thanks again CJ_London! the current query I have applies filter and check if users exist in table1. however if i want to add table1 additional fields to the query it stops to work. Is there a way to link and return additional fields from table1 corresponding to table 1 users records but only for matching records?

 

[CJ_London]

Perhaps you can post your full sql, I suspect your requirement is more complex that you are explaining.

In the meantime, try rewriting this:

WHERE (((Table2.users) In 
(SELECT Table1.users FROM Table1 GROUP BY Table1.users, Table1.mydate HAVING (((Table1.mydate)=#1/1/2013#)))) AND ((Table2.mydate)=#1/1/2013#)

To This

WHERE Table2.users IN
 (SELECT DISTINCT users FROM Table1 [COLOR=red]AS TMP[/COLOR] WHERE mydate =Table2.MyDate AND Users=Table2.Users)

 

[pedie]

CJ_London. thanks again for correcting me there.
Since is is late now and 'll try this tomorrow. and post back.
Good night!

 

[pedie]

CJ_London, thanks for helping it is working now....