SQL Inner join in MS Access 2007

Question

I am facing problem with Inner join in Access Database. I have two table having two column each. one is MID and other is DOB in both table. The name of first table is maintable and other is under5. I want to retrieve all those records that are present in under5 table after matching it with maintable so i used Inner join. the problem is there is a MID 10106 whose frequency in under5 table is 2 and in maintable it is 5 but after running the query the number of MID(10106) returned is 10( i guess the expected behaviour should be 2) which is really absurd. please help me to solve this problem. This is my Query:

SELECT maintable.MID, maintable.DOB, Under5.MID, Under5.DOB
FROM under5
INNER JOIN maintable ON under5.MID=maintable.MID;

Zohaib · Accepted Answer · 2011-11-17T07:16:20.293

1

The behaviour is abosolutely fine.

Every row in Main Table will be matched with Every Row in inner table. As there are 5 rows in Main Table And two rows in inner table 5*2 = 10 rows

Can there be any other way so that i could only retrieve those MID and DOB 
that is present in under5 table after matching it with maintable.

try this

SELECT distinct Under5.MID, Under5.DOB 
FROM under5, MainTable
where under5.mid = mainTable.mid

edited Nov 17 '11 at 07:16

answered Nov 17 '11 at 07:05

Zohaib

7,026
3
26
35

Can there be any other way so that i could only retrieve those MID and DOB that is present in under5 table after matching it with maintable. – Devrishi Nov 17 '11 at 07:09
Can there be any other way so that i could only retrieve those MID and DOB that is present in under5 table after matching it with maintable. – Devrishi Nov 17 '11 at 07:10
I tried Distinct clause but ended with the same problem. don't know why access is behaving this way – Devrishi Nov 17 '11 at 07:13
@user510686 please see edited answer above and see if it works ? – Zohaib Nov 17 '11 at 07:17

score 0 · Answer 2 · edited May 23 '17 at 11:47

0

You are describing a semijoin e.g. try:

SELECT Under5.MID, Under5.DOB
  FROM under5
 WHERE EXISTS (
               SELECT * 
                 FROM maintable 
                WHERE maintable.MID = under5.MID
              );

edited May 23 '17 at 11:47

Community

1
1

answered Nov 17 '11 at 08:26

onedaywhen

55,269
12
100
138

SQL Inner join in MS Access 2007

2 Answers2