SQL Query involving 4 tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

SQL Query involving 4 tables

Author

Topic

rckuhn
Starting Member

5 Posts

Posted - 2010-03-14 : 19:49:30

I need a query that will pull the email address, first name, last name from a contacts table with the following criteria.

The 4 tables involved are:

Projects
Clients
Secondary_Clients
Contacts

The Clients are linked to the Projects table with a clientID

The Secondary_Clients table holds a ProjectID and a ClientID to link additional clients to the project

the contacts are linked to the client table with a clientID field

I need to return all emails from the contacts table that match the client field in the projects table and the secondary_clients table and where the projectID matches in both the secondary_clients table and the projects table

hope that is clear.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-03-15 : 05:55:52

homework?
show us what you've tried first. we're not a homework answering service, but we will help you once you've shown evidence of helping yourself.

rckuhn
Starting Member

5 Posts

Posted - 2010-03-15 : 07:01:15

quote:
Originally posted by AndrewMurphy

homework?
show us what you've tried first. we're not a homework answering service, but we will help you once you've shown evidence of helping yourself.

Sorry I should have at least posted the latest SQL script

SELECT Contacts.email, Contacts.first_name, Contacts.last_name, Contacts.clientID, Projects_Secondary_Client.ProjectID
FROM (Projects_Secondary_Client INNER JOIN Contacts ON Projects_Secondary_Client.ClientID=Contacts.clientID) INNER JOIN Clients ON (Projects_Secondary_Client.ClientID=Clients.ClientID) AND (Clients.ClientID=Contacts.clientID)
WHERE (((Projects_Secondary_Client.ProjectID)=[project]));

This is currently in an access 2003 /2007 environment.

I have since changed the way it works to add a secondary and tertiary client field to the projects table and do it with just the projects table and the contacts table.

I would like to do it the other way to allow for no limitation on the number of secondary clients without needing to keep adding fields to the projects table.

I have also changed it to pull the [projects] parameter from a [form]![form_name]![form_field_name] to allow the entry of the parameter to be done without interaction.

It is working but I don't think it is the best way to do it!

BTW, good geuss on the homework but, I am just struggling through this for a helpful work project.

my primary job is engineering and it has been 15+ years since I have done any real programming. I wish I would have continued programming at some level this may have been a whole lot easier.

To add fuel to the flame, I am also trying to move the whole solution to a VB based visual studio project. Currently,Access seems to have much easier rapid development / quick solution tools...at least for what I am doing. My original plan of how this would be a great project to get back into the programming thing has had a few bumps.

Thanks for understanding and any assistance you can offer!

rckuhn
Starting Member

5 Posts

Posted - 2010-03-15 : 07:07:49

quote:
Originally posted by AndrewMurphy

homework?
show us what you've tried first. we're not a homework answering service, but we will help you once you've shown evidence of helping yourself.

here is the latest SQL script from the solution that works.

SELECT contacts.email, contacts.first_name, contacts.last_name, Projects.clientID, Projects.ProjectID, Projects.Secondary_ClientID
FROM contacts, Projects
WHERE (((Projects.clientID)=[contacts].[clientID]) AND ((Projects.ProjectID)=[forms]![form_name]![ProjectID])) OR (((Projects.ProjectID)=[forms]![form_name]![ProjectID]) AND ((Projects.Secondary_ClientID)=[contacts].[clientID])) OR (((Projects.ProjectID)=[forms]![form_name]![ProjectID]) AND ((Projects.tertiary_ClientID)=[contacts].[clientID]))
ORDER BY contacts.last_name;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 10:38:21

quote:
Originally posted by rckuhn
[i]
This is currently in an access 2003 /2007 environment.
[\i]

if this is access related quetion,please post in access forum

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources