sql question

  • Thread starter Thread starter Thomas L
  • Start date Start date
T

Thomas L

Hi,

i have an access database with a project table, a contacts table, and a
table which links several contacts to the project in a certain role. For
example it links Tom to the project as project leader, it links Larry to the
project as External Contact and Pete as Internal Contact.

Now i have a search form where i can find projects based on several data.
Now it's possible to search on one contact at a time. We want to make it
possible to search on 2 contacts at the same time.

The person who created this app made an sql-query with LEFT JOIN's from the
project table to the linktable. Now when i try to make it search on two
contacts, it doesn't return any data, allthough i know the data is there.

What i want to do is find a project where Tom and Larry are linked as
contacts.

Anyone able to give me a clue on how to do this?

Regards,

Thomas L.
 
Can you post the SQL for the query?


SELECT * FROM Table WHERE Contact = 'Tom' OR Contact = 'Larry'
 
Hi,

i wish it was that simple. But i have a table called Projects and a table
called Project_Contacts and a table called Contacts.

Now i have a record in Projects, and then i have several records in
Project_Contacts linking different contacts to that project.

For example in table Projects i have

Project_id---Project_name
1------------My Project

and then in Project_Contacts i have

Project_id-----Contact_id----Role
1------------------5----------Leader
1------------------6----------Representative
1------------------3----------Secretary

etc...

and in the table Contacts i have

Contact_id-------Name
3-----------------Ann
5-----------------Peter
6-----------------Charly

etc...

Now i want to search my database for the project where Ann AND Charly are
connected to. Not where one or the other is connected to.

REgards,

Thomas L.
 
Ahh, ok. In that case you might want to use subqueries:

SELECT DISTINCT Projects.ProjectName
FROM (Project_Contacts INNER JOIN Projects ON Project_Contacts.Project_ID =
Projects.Project_ID) INNER JOIN Contacts ON Project_Contacts.Contact_ID =
Contacts.Contact_ID
WHERE (((Projects.ProjectName) In (SELECT DISTINCT Projects.ProjectName FROM
(Project_Contacts INNER JOIN Projects ON Project_Contacts.Project_ID =
Projects.Project_ID) INNER JOIN Contacts ON Project_Contacts.Contact_ID =
Contacts.Contact_ID WHERE (((Contacts.ContactName)="ann"));)) AND
((Contacts.ContactName)="charlie"));


This would return a list of ProjectNames which Ann & Charlie are working on.
It is a little unwieldy though...
 
Back
Top