SQL Query Issue - 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

General SQL Server Forums

New to SQL Server Programming

SQL Query Issue

Author

Topic

nemesis1814
Starting Member

4 Posts

Posted - 2011-04-20 : 06:20:06

Hello,

I have a database with customers, jobs and technicians. (There is composite key containing CustID, TechID and JobID to link these fields) Each job only has 2 technicians allocated to it. I was wondering if it is possible to create a query showing a list of who a particular technician has worked with?

Thank You.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-20 : 06:39:42

So both technicians will have same JobID ??

--------------------------
http://connectsql.blogspot.com/

nemesis1814
Starting Member

4 Posts

Posted - 2011-04-20 : 06:43:08

Yes..

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-20 : 06:45:31

CREATE TABLE #TestTable (JobID INT, TechID INT)
INSERT INTO #TestTable
SELECT 1,202 UNION ALL
SELECT 1,206 UNION ALL
SELECT 3,811 UNION ALL
SELECT 3,177

DECLARE @FindTechPartner INT
SET @FindTechPartner = 177
SELECT *
FROM #TestTable
WHERE JobID IN ( SELECT JobID
FROM #TestTable
WHERE TechID = @FindTechPartner )
AND TechID <> @FindTechPartner

DROP TABLE #TestTable

--------------------------
http://connectsql.blogspot.com/

nemesis1814
Starting Member

4 Posts

Posted - 2011-04-20 : 06:48:48

I have no clue what those numbers mean.. But can i incorporate this code into my current database?

Also will this work in MS Access?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-20 : 06:54:28

well I am not sure about MS Access

--------------------------
http://connectsql.blogspot.com/

nemesis1814
Starting Member

4 Posts

Posted - 2011-04-20 : 06:58:00

No worries, thanks a lot for your help.