inner join query problem

[estersita]

Hi

I have a small table Source (100 rec) and I have a big table MedClaim (3000 rec)

Some of Souce IDs are the same as in MedClm. Also Med Clm have a lot of duplicates.

All I want is just to add some additional fields from Medclm to Source in order to add certain fileds from MedClm to Source . I want Sorce have 100 records as before.

I use Inner join for ID fields but for some reason I got all records from MEDCLM.

I opened properties for join and tried all 3 options and still have ALL records from MEDCLM

Select *
FROM source INNER JOIN MEDCLM ON source.ID = MEDCLM._ID;

What I am doing wrong?

Thank you!

 

[traingamer]

Try a where clause - you are asking for ALL records and the database is responding with all records.
Also you may want source.*, MEDCLM.Flds...

traingamer

 

[PHV]

I got all records from MEDCLM
Even records with MEDCLM._ID <> source.ID ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[estersita]

I included both source.id and medclm.id fields into my query
and they are all equal. It looks like that happened because
there are a lot of duplicate IDs in the bigger table MEDclm which ALL match SOURCE.ID!

So, in order to add a couple of fields from MEDCLM one have to have criteria based on other fields except ID!