Re: New to SQL and trying multiple table joins

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 07/26/04 

Date: Mon, 26 Jul 2004 17:30:57 +0100

This should be close to what you need.

SELECT E.ssn, E.ename, EA.eamonthid, EA.eayearid, D.abilitycode,
 J.jobid, J.jobtitle, C.companyname
 FROM Emp AS E
 JOIN EmpAbilities AS AB
  ON AB.empid = E.empid
   AND AB.mainability = -1
 JOIN def_ability AS D
  ON D.abilityid = AB.abilityid
 JOIN EmpAvail AS EA
  ON EA.empid = E.empid
 LEFT JOIN CurrentJob AS CJ
  ON CJ.empid = E.empid
   AND CJ.cjmonthid = EA.eamonthid
   AND CJ.cjyearid = EA.eayearid
 LEFT JOIN Job AS J
  ON J.jobid = CJ.jobid
 LEFT JOIN Contact AS CT
  ON CT.contactid = J.contactid
 LEFT JOIN Company AS C
  ON C.companyid = CT.companyid
 WHERE EA.eamonthid = 7
  AND EA.eayearid = 2004

It can help you get faster, better answers if you include table structures
as DDL (CREATE TABLE statements) and sample data as INSERT statements:

CREATE TABLE Emp (empid INTEGER PRIMARY KEY, ssn CHAR(12) NOT NULL UNIQUE,
ename VARCHAR(30) NOT NULL, eaddress VARCHAR(40) NOT NULL)

INSERT INTO Emp VALUES (1234, '555 55 5555', 'Dave Smith', '123 This Place')

That makes it quicker and easier for others to understand the problem and to
test out possible solutions.

Hope this helps. 

-- 
David Portas
SQL Server MVP
--

Relevant Pages