another join!

[AshleyH]

I have this:

cfquery name="rsSecContacts" datasource="#request.dsn#">
SELECT
tbl_contacts.id,
tbl_contacts.con_Customer,
tbl_contacts.con_CompTel,
tbl_contacts.con_CompFax,
tbl_contacts.con_CompEmail,
tbl_contacts.con_CompWeb,
tbl_contacts.con_MainCont,
tbl_contacts.con_Address1,
tbl_contacts.con_Address2,
tbl_contacts.con_Address3,
tbl_contacts.con_Address4,
tbl_contacts.con_Address5,
tbl_contacts.con_mobile,

tbl_ContactSecondary.sec_id,
tbl_ContactSecondary.sec_company,
tbl_ContactSecondary.sec_name,
tbl_ContactSecondary.sec_tel,
tbl_ContactSecondary.sec_mobile,
tbl_ContactSecondary.sec_DDI,
tbl_ContactSecondary.sec_email,
tbl_ContactSecondary.sec_fax,
tbl_ContactSecondary.sec_address1,
tbl_ContactSecondary.sec_address2,
tbl_ContactSecondary.sec_address3,
tbl_ContactSecondary.sec_address4,
tbl_ContactSecondary.sec_mainid,

tbl_ContactSubSecondary.id,
tbl_ContactSubSecondary.subsec_company,
tbl_ContactSubSecondary.subsec_name,
tbl_ContactSubSecondary.subsec_tel,
tbl_ContactSubSecondary.subsec_mobile,
tbl_ContactSubSecondary.subsec_DDI,
tbl_ContactSubSecondary.subsec_email,
tbl_ContactSubSecondary.subsec_address1,
tbl_ContactSubSecondary.subsec_address2,
tbl_ContactSubSecondary.subsec_address3,
tbl_ContactSubSecondary.subsec_address4,
tbl_ContactSubSecondary.subsec_mainid,
tbl_ContactSubSecondary.subsec_secid

FROM tbl_Contacts

INNER 
  JOIN tbl_ContactSecondary
    ON tbl_ContactSecondary.sec_mainid = tbl_contacts.ID
    
     
INNER
  JOIN tbl_ContactSubSecondary
    ON tbl_ContactSubSecondary.subsec_mainid = tbl_ContactSecondary.sec_mainid

</cfquery>

What I need to do is join the '_mainid' columns in the tbl_ContactSecondary, tbl_ContactSubSecondary to the 'tbl_contacts.id'.

I have tried so many combinations but just either get 1 record pulled out or none! These things drive me nuts! The code above looks like it should work but it's not quite there!

Any help greatfully received!

Ashley

[r937]

please give a couple of sample rows of data which illustrate your problem (just the pertinent columns, if you want to cut it down a bit)