We help IT Professionals succeed at work.

coldfusion inner join

jameskane
jameskane used Ask the Experts™
on
I have an access database and am trying to create a query which joins two of the tables in that database. Both tables have field "nom" and I wish to join them on this field. The names of the two tables are MEMBERS2 and act_members. I need the record set to contain values of the field naissance which exists only in MEMBERS2.

I attach part of the record set produced. The problem is that the total number of records in the recordsed is 350, when in fact it should not be more than 313 - which is the number of records in act_members.

I note that there is duplication of records in the recordset.

Can anyone throw some light on this ?

Many thanks

james kane

THIS IS THE QUERY

<cfquery datasource = "office_10" name="thelist">
SELECT MEMBERS2.naissance, act_members.nom
FROM act_members
INNER JOIN MEMBERS2
ON act_members.nom = MEMBERS2.Nom
</cfquery>
<cfquery datasource = "office_10" name="thelist">
SELECT MEMBERS2.naissance, act_members.nom
FROM act_members
INNER JOIN MEMBERS2
ON act_members.nom = MEMBERS2.Nom
</cfquery>

Open in new window

RECORDSET.jpg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Commented:
>> ON act_members.nom = MEMBERS2.Nom

Likely one of 2 things is happening. Either the join is correct and you just need to add a SELECT DISTINCT OR the tables are related by more than just the "nom" column. So you're ending up with a partial cartesian product.  

- What does "nom" represent in plain english? Is the value unique?
- How many records are in MEMBERS2?  

Author

Commented:
Hi agx, nice to hear from you again !! and as usual thanks for the help !!

nom represents name. There are doubles unfortunately, but they are differentiated by prenom. So, a husband and wife might both be individually registered. nom is the same for both, but prenom is different.

MEMBERS2 have 465 records
act_members has 313 records.

Unfortunatly I did screw up the design a bit. There is no key field in the active members table, but there is in the MEMBERS2 table memberID.  

Author

Commented:
I just noted that MEMBERS2 has "Nom" as field name while act_members has "nom"

> I just noted that MEMBERS2 has "Nom" as field name while act_members has "nom"

Case doesn't matter for the field name.

As agx suggested, if there is more than one column to make the record unique, you need to join by both of them, you mentioned prenom, so here is the query with prenom added.

<cfquery datasource = "office_10" name="thelist">
  SELECT distinct MEMBERS2.naissance, act_members.nom
    FROM act_members
   INNER JOIN MEMBERS2  ON act_members.nom = MEMBERS2.Nom
                                        and act_members.Prenom = MEMBERS2.PreNom
</cfquery>

If you don't have prenom in both tables, then your onlyother option is to add SELECT DISTINCT, which I did above anyway.

> Unfortunatly I did screw up the design a bit. There is no key field in the active members table, but there is in the MEMBERS2 table memberID.  

It's not too late :)

Add a primary key in the active members table  ACTMEMBERID and then add a foreign key to the MEMBERS table MEMBERID*.   You can match them up using the nom and prenom fields and then manually clean-up and problems.    Then you've fixed it for on-going use !

* this assumes that a member can have more than one active_member, if you're going to add the key we should explore which way the one-to-many relationship should go.


Most Valuable Expert 2015

Commented:
cheers "Go Foreign keys!Go Foreign keys!Go Foreign keys!" ;-)

But seriously that's a good design option to explore.

Author

Commented:
Many thanks to you both !!!!

Well this is a project that has finished - don't expect any more developments. So, I'll pass on the  upgrade. BUT, thanks to people like you both, I have learned a lot over the past few  years. I  don't make mistakes like that any more !!

gdemaria, tried the code and it does the trick !!

Thanks to you both again.

James

Author

Commented:
Thanks guys !!
Most Valuable Expert 2015

Commented:
Yep, it's not always worth it for legacy applications.  But like you said, knowledge can always be applied the next project.  

Have a great weekend!
Cheers