Join 4 tables

[aw23]

Is it possible to join more than 2 tables in a query. I have the following query:

SELECT company.Com_Name, customers.C_Email
FROM customers LEFT JOIN company ON customers.Company_ID = company.Company_ID
WHERE (((company.Company_ID) In (537,5471)));

Now I need to add two more tables. I need to get country.cntry, and emp.employee

both of which have id's in the company table.
Is this possible in one query? I need it to be in one query because the results are diplayed on a bound form which is set to a query.

Thanks!

 

[PHV]

A starting point:
SELECT company.Com_Name, customers.C_Email, country.cntry, emp.employee
FROM ((company
INNER JOIN customers ON company.Company_ID = customers.Company_ID)
INNER JOIN country ON company.country_ID = country.country_ID)
INNER JOIN emp ON company.emp_ID = emp.emp_ID
WHERE company.Company_ID In (537,5471);

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

 

[aw23]

Wow, that worked! Thank you so much. By the way, what's the difference between an inner join and a left join?

Thanks

 

[PHV]

A left join is an outer join, ie it selects also the rows of the preceding table that don't meets the ON clause.

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

 

[aw23]

Thanks
I just ran my code and I have an error. I opened the subform with that query (although there are several more fields which I did not include in the post) and I got the following error:

'The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.'

The error is on the following line

Me![form].Form.RecordSource = sql
Does this mean I can't enter do much data?

Thanks

 

[PHV]

Seems your sql string is too big.
Try to shorten it using aliases or use a saved query.

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

 

[aw23]

Can you please direct me?
I don't know how to do that?
Thanks

 

[PHV]

Can you please direct me?
Can you please post the code populating the sql string ?

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

 

[aw23]

sql = "SELECT company.Com_Name, company.NoEmail, customers.NoEmail, customers.C_FName, customers.C_LName, customers.C_Email, c.nc, employees.efirst_name" _
& "FROM ((company" _
& "INNER JOIN customers ON company.Company_ID = customers.Company_ID)" _
& "INNER JOIN c ON company.country_ID = c.c)" _
& "INNER JOIN employees ON company.com_rep = employees.employees_ID" _
& "WHERE (instr(c_email," & "'@'" & ")>0) AND (company.Company_ID IN (537,657,3434)) order by company.com_name" "

 

[PHV]

You may try this:
sql = "SELECT A.Com_Name,A.NoEmail,B.NoEmail,B.C_FName,B.C_LName,B.C_Email,c.nc,E.efirst_name" _
& " FROM ((company A" _
& " INNER JOIN customers B ON A.Company_ID=B.Company_ID)" _
& " INNER JOIN c ON A.country_ID=c.c)" _
& " INNER JOIN employees E ON A.com_rep=E.employees_ID" _
& " WHERE InStr(c_email,'@')>0 AND A.Company_ID IN (537,657,3434) ORDER BY 1"

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

 

[aw23]

Thanks,
I received the following error:

'The record source 'SELECT A.Com_Name,A.NoEmail,B.NoEmail,B.C_FName,B.C_LName,B.C_Email,c.nc,E.efirst_name FROM ((company A INNER JOIN customers ...' specified on this form does not exist