sql server programming: 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, SSN, EName, EAddress
EmpAvail: EmpAvailID, EmpID, EAMonthID, EAYearID
Abilities: AbilityID, EmpID, MainAbility (0/-1)
CurrentJob: CJID, JobID, EmpID, AbilityID, CJMonthID, CJYearID
Job: JobID, JobTitle, ContactID, JMonthID, JYearID
Contact: ContactID, CompanyID, CName, CAddress
Company: CompanyName, CompDesc, CompURL
def_ability: AbilityID, AbilityCode, AbilityDesc
def_Month: MonthID, MonthCode
def_Year: YearID, 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.

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.

--
David Portas
SQL Server MVP
--

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

[quoted text, click to view]

Sample data is no issue, but I was worried about the DDL. I can guess by
what access tells me, but I do not have exactly what it is. I do know that
in the CurrentJob the ODBC manager always asks me to select keys.. since I
have no access to the database after hours I will have to defer this until
Monday. Thank you for your assistance!

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

[quoted text, click to view]

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 spreadsheet 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

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

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in
news:Gfmdnbyasf1ErJjcRVn-oQ@giganews.com:

[quoted text, click to view]

For some reason this did not work, but never fear I did find something that
did work. I created two queries in Access, and then used a Union and for
some odd reason it actually worked properly this time. I am expecting a
complete data map from the provider soon, and with that I may repost this
question since I just do not feel the query I have written is efficient
enough.

If and when you want more help, please give more information than "did not
work". Error message? Unexpected results?

--
David Portas
SQL Server MVP
--

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in
news:6pednayA3bb4Y5jcRVn-uw@giganews.com:

[quoted text, click to view]

Sorry I was kind of elated that the method I attempted worked. Basically
when I plugged that query into Access it came back that the join statement
was invalid.

This is a SQL Server group so I wrote a SQL Server query. Access has
different limitations and syntax. You should be able to execute the code I
posted as a pass-through query in Access.

--
David Portas
SQL Server MVP
--