Re: New to SQL and trying multiple table joins

From: Dave R. (crombieNOSPAM26_at_hotmail.com)
Date: 07/26/04 

Date: Mon, 26 Jul 2004 14:28:53 GMT

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in
news:j4KdnQ2WVbT-9pzcRVn-og@giganews.com:

> I think you will need to convert some of your INNER joins to OUTER
> joins. That should capture both those who do and don't have a job.
> Can't be certain of how to write this query for you without DDL (CREATE
> TABLE statements) for all these tables and maybe some sample data as
> well. At minimum we really need to know what the Primary and Foreign
> keys are.
>

Previous msg edited plus added in keys, and sample data at end. If you need
the data in a cleaned-up format I do have a spread*** I can upload
somewhere as well:
"Unfortunately I have been thrust into the position of learning SQL for a
new database we have implemented. I have very basic SQL knowledge, and can
do some simple queries, but with the amount of tables I am working with it
seems almost out of my scope.

Here are the basics:

I am working with 10 tables, and I have no access to make any changes to the
table structure:
Emp: EmpID (Primary Key), SSN, EName, EAddress
EmpAvail: EmpAvailID (Primary Key), EmpID (Foreign Key?), EAMonthID(Foreign
Key?), EAYearID(Foreign Key?)
EmpAbilities: AbilityID, EmpID(Foreign Key?), MainAbility (0/-1)
CurrentJob: CJID, JobID(Foreign Key?), EmpID(Foreign Key?), AbilityID
(Foreign Key?), CJMonthID(Foreign Key?), CJYearID(Foreign Key?)
Job: JobID, JobTitle, ContactID(Foreign Key?), JMonthID(Foreign Key?),
JYearID(Foreign Key?)
JobAbilities: JobID(Foreign Key?), AbilityID(Foreign Key?)
Contact: ContactID(Primary Key), CompanyID(Foreign Key?), CName, CAddress
Company: CompanyID (Primary Key), CompanyName, CompDesc, CompURL
def_ability: AbilityID(Primary Key), AbilityCode, AbilityDesc
def_Month: MonthID(Primary Key), MonthCode
def_Year: YearID(Primary Key), YearCode

What I have been trying unsuccessfully to do is take the Work table, and
join everthing back into that table so that instead of:
2, 2345, 55674, 1, 2004

It expands with the other tables:
WorkID, JobID->[JobTitle, JMonthID->(MonthCode), JYearID->(YearCode),
ContactID->(CompanyID->(CompanyName, CompDesc, CompURL), CName,
CAddress)],EmpID->(SSN, Name, Address) & -> EmpAvail(EAMonthID->(MonthCode),
EAYearID->(YearCode)), AbilityID->(AbilityCode, AbilityDesc, MainAbility)

Where the result will look like:
SSN, EName, CJMonthID, CJYearID, JobID, JobTitle, CompanyName, AbilityCode

Here is the SQL I use right now that does this for me, but I am not certain
if it is proper:
SELECT c.CompanyName, e.SSN, e.Ename, j.jobid, j.JobTitle, d.abilitycode
FROM
def_ability AS d,
Company AS c,
Emp as e,
CurrentJob AS cj,
Contact AS ct,
Job AS j
WHERE e.Empid=cj.Empid
And j.jobid=cj.jobid
And j.contactid=ct.contactid
And ct.companyid=c.companyid
and d.abilityid=cj.abilityid
And cj.monthid=1
And cj.yearid=2004;

Finally now I have to go back, and determine who does not have a job, and
combine those two lists into one. The trick is I have to do it with one
query. I just do not have enough understanding of subqueries to get to where
I want to be. Can anyone help me out, or give me suggestions on the best
place to look to start understanding this?

Note: The names of the fields are changed, but the structure of those tables
remain the same from my current database"

Here is the sample data with the expected result at the end:
Emp
EmpID SSN Ename Eaddress
1234 555 55 5555 Dave Smith 123 This Place
1235 666 66 6666 Jim Smith 124 This Place
1236 777 77 7777 Joe Smith 125 This Place
EmpAvail
EmpAvailID EmpID EAMonthID EAYearID
1 1234 7 2004
2 1234 8 2004
3 1235 7 2004
4 1236 7 2004
EmpAbilities
AbilityID EmpID MainAbility
1 1234 -1
2 1234 0
3 1234 0
1 1235 0
2 1235 -1
3 1235 0
3 1236 -1
Job
JobID JobTitle ContactID JMonthID JYearID
1 Carpenter's Apprentice 1 7 2004
2 Electrician's Apprentice 2 7 2004
3 Management Apprentice 3 7 2004
4 Other Apprentice 3 7 2004
JobAbilities
JobID AbilityID
1 1
2 2
3 3
4 4
Contact
ContactID CompanyID CName CAddress
1 2 Joe Jones 123 Anywhere St
2 3 Dave Jones 124 Anywhere St
3 1 Bob Jones 125 Anywhere St

Company
CompanyID CompanyName CompDesc CompURL
1 Bob Jones' Carpentry Carpentry business Null
2 Dave Jones' Electrical Electrical business Null
3 Joe Jones' Quality Temps Temp Agency Null
CurrentJob
CJID JobID EmpID AbilityID CJMonthID CJYearID
1 1 1234 1 7 2004
2 3 1236 3 7 2004
def_ability
AbilityID AbilityCode AbilityDesc
1 CARP Carpentry
2 ELEC Electrical
3 MGMT Management
4 OTHR Other
5 PLMB Plumbing
6 PRGM Programming
7 UNDT Undetermined
def_Month
MonthID MonthCode
1 January
2 February
3 March
4 April
5 May
6 June
7 July
8 August
9 September
10 October
11 November
12 December
def_Year
YearID YearCode
2000 2000
2001 2001
2002 2002
2003 2003
2004 2004
2005 2005
2006 2006
2007 2007

Expected Result:
SSN EName EAMonthID EAYearID PrimaryAbilityCode JobID JobTitle
        CompanyName
555 55 5555 Dave Smith 7 2004 CARP 1 Carpenter's Apprentice
        Bob Jones' Carpentry
666 66 6666 Jim Smith 7 2004 ELEC
777 77 7777 Joe Smith 7 2004 MGMT 3 Management Apprentice Joe
Jones' Quality Temps