Query on Multiple Fields from Multiple Tables

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 2000 Forums

SQL Server Development (2000)

Query on Multiple Fields from Multiple Tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-12 : 09:25:31

Doug writes "I am only a few months into SQL and have had great success with it, but I am stumped. I may just not be looking at this the right way. Please forgive me if this is stupid.

I have a need to search across 4 tables (or more) using one query. I want to join those tables at 3 specific fields.
I will truncate the fields to just what is pertinant.


Table1 (4 columns)
JobID(PK)       JobDesc     CustomerID  ProjMngrID   EngineerID

Table2 (2 columns)
CustomerID(PK)  CustomerName

Table3 (2 columns)
ProjMngrID(PK)  ProjMngrName

Table4 (2 columns)
EngineerID(PK)  EngineerName

I want to be able to search by Job Description, Customer Name, Engineer Name or Project Manager Name all in one SQL statement and return the JobID for any jobs that match.

With the names being in other tables I have had a terrible time getting this to work.

I can get the joins to work fine without the search query "LIKE" in the following query...the problem is when I introduce the like clause to search for certain names.

SELECT Jobs.OrderNum, Jobs.StartDay, Jobs.StopDay, Jobs.JobDollars, Employees.EmpName, Employees_1.EmpName, Customers.CustName, Jobs.Description, Jobs.JobStatus
FROM Employees AS Employees_1 INNER JOIN (Employees INNER JOIN (Customers INNER JOIN Jobs ON Customers.CustID = Jobs.CustID) ON Employees.EmpID = Jobs.EmpID) ON Employees_1.EmpID = Jobs.PMID;

Can you help me? Or am I beyond help?"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources