Showing Indirectly Related field

[prophecym]

Please see below for table details.

 

I have these tables have relations in each other and I need a report using INNER JOIN. I figured out the most but there is one thing I couldn't.

 

There is CompanyName field in the Companies Table I need to show in the report but it is only related to Contacts Table by ContactID. How can I get it to show on my report? I hope I made myself clear.

 

Here is the SQL sytnax.

 

SELECT accounttypes.accounttype, contacts.firstname, contacts.lastname, contacts.namesuffix, contacts.emailname, contacts.workphone, speakers.programtrack, speakers.recruitedby
FROM regcontacts
INNER JOIN accounttypes ON regcontacts.accounttypeid = accounttypes.accounttypeid
INNER JOIN contacts ON regcontacts.contactid = contacts.contactid
INNER JOIN speakers ON regcontacts.registrationid = speakers.registrationid
WHERE regcontacts.eventid =  '235'
AND regcontacts.participationid =  '3'

 

 

table RegContacts fields :

AccountTypeID

ContactID

RegistrationID

---

table AccountTypes fields :

AccountTypeID

---

table Contacts fields :

ContactID

CompanyID

---

table Speakers fields :

RegistrationID

---

table Companies fields :

ContactID

CompanyID

[prophecym]

It took me 30 seconds to figure it out. If i had spent my time on thinking, instead of typing all colorful, fancy shmancy topic, i would have created my report. Well what's done is done.

 

Here is the right syntax.

SELECT accounttypes.accounttype, contacts.firstname, contacts.lastname, contacts.namesuffix, contacts.emailname, contacts.workphone, speakers.programtrack, speakers.recruitedby, companies.companyname
FROM regcontacts
INNER JOIN accounttypes ON regcontacts.accounttypeid = accounttypes.accounttypeid
INNER JOIN contacts ON regcontacts.contactid = contacts.contactid
INNER JOIN speakers ON regcontacts.registrationid = speakers.registrationid
INNER JOIN companies ON contacts.companyid = companies.companyid
WHERE regcontacts.eventid =  '235'
AND regcontacts.participationid =  '3'
LIMIT 0 , 30