Join two table query with third table

[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

 

[Dale Brown]

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 Patel]

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

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

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

 

[John Viescas]

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)