Join two table query with third table

  • Thread starter Thread starter Shailesh Patel
  • Start date Start date
S

Shailesh Patel

Dear:
In the following query, Query5 is two table join query.
And when I try to join with 3rd table (Contacts), it returns only one record
instead of multiple records from Contacts table. I tried to change INNER to
LEFT or RIGHT but returns only one record instead of more match with that
criteria.
Any comment/help is appreciated.

SELECT Contacts.[First]+' '+Contacts.[Last] AS Expr1, Query5.*
FROM Query5 INNER JOIN Contacts ON Query5.ProspectId = Contacts.ProspectID
WHERE (((Query5.Company) Like 'kuhl*') AND ((Contacts.Title) Like 'ceo*'));

Thank you in advance.

Shailesh
 
Shailesh,

I am assuming there is multiple contact records you want to see returned
from this query. Try switching the contact and query5 in the from and
inner join statements.

Dale Brown
 
Still same result. Did not return all related rows from Contacts table.
Let me know.

Dale Brown said:
Shailesh,

I am assuming there is multiple contact records you want to see returned
from this query. Try switching the contact and query5 in the from and
inner join statements.

Dale Brown

Shailesh said:
Dear:
In the following query, Query5 is two table join query.
And when I try to join with 3rd table (Contacts), it returns only one record
instead of multiple records from Contacts table. I tried to change INNER to
LEFT or RIGHT but returns only one record instead of more match with that
criteria.
Any comment/help is appreciated.

SELECT Contacts.[First]+' '+Contacts.[Last] AS Expr1, Query5.*
FROM Query5 INNER JOIN Contacts ON Query5.ProspectId = Contacts.ProspectID
WHERE (((Query5.Company) Like 'kuhl*') AND ((Contacts.Title) Like 'ceo*'));

Thank you in advance.

Shailesh
Click to expand...
Click to expand...
 
You have basically asked for:

"Show me the company information and related contact information for
companies whose name starts with 'kuhl' and the related contact has a title
that starts with 'ceo'."

If there is only one company with a name that starts with 'kuhl' and that
company has only one related contact with a title that starts with 'ceo',
you'll get only one row. What do you expect to get?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top