Left join and intersection table 2

[evergreean43]

I have a table called Jobs with job information.
I have two feeder tables with one called Persons (workers names) and Technology(with technology listing) where each Person has to have a Technology if a Person is assigned a Job.
But not every job will have a Person/Technology assignment.
Some jobs have no Person/Technology assignment and some do..
My Tables in Access 2000 with example values:

Jobs                          Persons
job_id   jobName              person_id   personName
1        First name           1           Jones
2        Another job          2           Smith
3        title of job         3           Carson
4        next job here        4           Miller
5        main job

Technology                    IntersectionTable 
tech_id   techName            job_id  person_id  tech_id
1         Cold Fusion         2       3          2   
2         Visual Basic        3       1          3
3         Perl                3       3          2
4         PHP                 5       2          1

To tie everything together I use an Intersection table which uses Jobs foreign key and
the feeder table values to track Jobs and who has worked on the job and what technology they use.
Not all of the Jobs have Persons/Technolody so I use a left join to fetch Job info and Intersection
table info so I can get all Jobs with and without Person/Technology info.
This works with no problem:

SELECT *
FROM jobs 
left JOIN intersectionTable 
ON jobs.job_id = intersectionTable.job_id

But I need to fetch the feeder tables into my results.

This will combine the feeder tables together using the intersection table but doesnt include the
Jobs table:

SELECT *
FROM technology 
INNER JOIN (persons 
INNER JOIN intersectionTable 
ON persons.person_id = intersectionTable.person_id) 
ON technology.tech_id = intersectionTable.tech_id

Here is my attempt to include all but it gives me syntax errors:

SELECT *
FROM jobs 
LEFT JOIN intersectionTable 
ON jobs.job_id = intersectionTable.job_id
(SELECT *
FROM technology 
INNER JOIN (persons 
INNER JOIN intersectionTable 
ON persons.person_id = intersectionTable.person_id) 
ON technology.tech_id = intersectionTable.tech_id)

Please advise.

 

[lespaul]

I'm not sure that your setup "feels" right to me.

Why wouldn't you have a table People_Technology that records a many to many relationship of people and the things they can do and a Jobs_Technology table that records the technologies required for the jobs and then a third intersection table that records People_Job_Assignments? I thikn that with your tables configure like that you'd be able to get the data you are looking for much easier.

This way you have a listing of what people are able to do the work and as people add skills and technology, you can easily add that information to the table and make that person available for use the next time a job requires it.

Is it still possible to modify your tables?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[evergreean43]

Thanks for the info.

Unfortunately I cannot modify my tables.

Please advise.

 

[lespaul]

in that case can you provide an example of what you want your results to be based on the sample data given above?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[evergreean43]

Yes - thanks!


My query will be for all my jobs and I would want to get this output.

job_id  jobName        personName techName       person_id  tech_id
1       First name
2       Another job    Carson     Visual Basic   3          2
3       title of job   Jones      Perl           1          3
3       title of job   Carson     Visual Basic   3          2
4       next job here
5       main job       Smith      Cold Fusion    2          1

 

[lespaul]

This should do it:

SELECT Job.Job_ID, JobName, PersonName, TechName, I1.Person_ID, I2.Tech_ID
FROM Job
LEFT JOIN IntersectionTable ON Job.Job_ID = IntersectionTable.Job_ID
INNER JOIN IntersectionTable I1 ON Persons.Person_ID = I1.PersonID
INNER JOIN IntersectionTable I2 ON Technology.TEch_ID = I1.Tech_ID



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

And what about this (typed, untested):
SELECT J.job_id, J.jobName, X.personName, X.techName, X.person_id, X.tech_id
FROM jobs AS J LEFT JOIN (
SELECT I.job_id, P.personName, T.techName, I.person_id, I.tech_id
FROM (intersectionTable AS I
INNER JOIN Persons AS P ON I.person_id = P.person_id)
INNER JOIN Technology AS T ON I.tech_id = T.tech_id
) AS X ON J.job_id = X.job_id
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[evergreean43]

Thanks the one by PHV works great and still playing with the one by LesPaul. I appreciate the solutions.

Can I add another table to your query if I needed to get Customer info? The cust_id is a foreign key in Jobs table and every Job has to have a Customer ID. The Customer table has the customer id and name.

The output if I was to grab everything would be:

job_id jobName        Cust_id personName techName      person_id tech_id
1      First name     1
2      Another job    2       Carson     Visual Basic  3          2
3      title of job   1       Jones      Perl          1          3
3      title of job   1       Carson     Visual Basic  3          2
4      next job here  3
5      main job       2       Smith      Cold Fusion   2          1

My attempt below says "syntax error missing operator":

SELECT J.job_id, J.jobName, X.personName, X.techName, X.person_id, X.tech_id, C.cust_id
FROM jobs AS J LEFT JOIN (
SELECT I.job_id, P.personName, T.techName, I.person_id, I.tech_id
FROM (intersectionTable AS I
INNER JOIN Persons AS P ON I.person_id = P.person_id)
INNER JOIN Technology AS T ON I.tech_id = T.tech_id
) AS X ON J.job_id = X.job_id
(INNER JOIN Customer on C.cust_id = J.cust_id)
ORDER BY 1

Customer table:

Cust_id  CustName
1        Jennings
2        Baker
3        Foster

 

[lespaul]

if you are going to do:

C.Cust_id

then you need to tell the query what C is:

SELECT J.job_id, J.jobName, X.personName, X.techName, X.person_id, X.tech_id, C.cust_id
FROM jobs AS J
INNER JOIN Customer AS C on C.cust_id = J.cust_id
LEFT JOIN (
SELECT I.job_id, P.personName, T.techName, I.person_id, I.tech_id
FROM (intersectionTable AS I
INNER JOIN Persons AS P ON I.person_id = P.person_id)
INNER JOIN Technology AS T ON I.tech_id = T.tech_id
) AS X ON J.job_id = X.job_id

ORDER BY 1

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

No new join needed !
SELECT J.job_id, J.jobName, J.cust_id, X.personName, X.techName, X.person_id, X.tech_id
FROM jobs AS J LEFT JOIN (
SELECT I.job_id, P.personName, T.techName, I.person_id, I.tech_id
FROM (intersectionTable AS I
INNER JOIN Persons AS P ON I.person_id = P.person_id)
INNER JOIN Technology AS T ON I.tech_id = T.tech_id
) AS X ON J.job_id = X.job_id
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[evergreean43]

Thanks again.

I tried this:

SELECT J.job_id, J.jobName, X.personName, X.techName, X.person_id, X.tech_id, C.cust_id
FROM jobs AS J 
INNER JOIN Customer AS C on C.cust_id = J.cust_id
LEFT JOIN (
SELECT I.job_id, P.personName, T.techName, I.person_id, I.tech_id
FROM (intersectionTable AS I
INNER JOIN Persons AS P ON I.person_id = P.person_id)
INNER JOIN Technology AS T ON I.tech_id = T.tech_id
) AS X ON J.job_id = X.job_id
ORDER BY 1

It gave me an error saying:
"Syntax error (missing operator) in query expression"


This one only grabbed the cust_id in the Jobs table and didnt seem to grab any Customer table info:

SELECT J.job_id, J.jobName, J.cust_id, X.personName, X.techName, X.person_id, X.tech_id
FROM jobs AS J LEFT JOIN (
SELECT I.job_id, P.personName, T.techName, I.person_id, I.tech_id
FROM (intersectionTable AS I
INNER JOIN Persons AS P ON I.person_id = P.person_id)
INNER JOIN Technology AS T ON I.tech_id = T.tech_id
) AS X ON J.job_id = X.job_id
ORDER BY 1

Please advise.

 

[PHV]

You didn't mention the customer name:
The output if I was to grab everything would be:
job_id jobName Cust_id personName techName person_id tech_id
Anyway:
SELECT J.job_id, J.jobName, J.Cust_id, C.CustName, X.personName, X.techName, X.person_id, X.tech_id
FROM (jobs AS J
INNER JOIN Customer AS C ON J.Cust_id = C.Cust_id)
LEFT JOIN (
SELECT I.job_id, P.personName, T.techName, I.person_id, I.tech_id
FROM (intersectionTable AS I
INNER JOIN Persons AS P ON I.person_id = P.person_id)
INNER JOIN Technology AS T ON I.tech_id = T.tech_id
) AS X ON J.job_id = X.job_id
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[evergreean43]

Thank you very much!!!!!

It works great and it is such a great query I was able to add another inner join to it.

This was a great learning experience.

I want to give each of you a couple of thank you stars but I think I am limited to just one for each of you.