-
Oct 25th, 2021, 03:03 PM
#1
Thread Starter
Frenzied Member
query help (JOIN?)
Hi all.
I'm pretty familiar with LEFT JOIN, but this probably isn't that.
I have two tables, tblJOBS and tblEMPLOYEES.
tblJOBS is all about the jobs, and has one column (EMPLOYEE_ID) that links an employee in tblEmployees to a given job.
Ordinarily, I would select columns from both tables in a left join (from tblJOBS LEFT JOIN tblEMPLOYEES) to build the recordset I need.
As I understand LEFT JOIN, I will still get the records from tblJOBS even if there is no value in Employee_ID.
But I also need a record in the table if there happens to be an employee in tblEMPLOYEES who is not linked to a job in tblJOBS.
That row of data would have blanks for all the tblJOBS fields and information for all the tblEMPLOYEES fields.
Is there come kind of fancy join that makes this result?
Thanks.
Wen Gang, Programmer
VB6, QB, HTML, ASP, VBScript, Visual C++, Java
-
Oct 25th, 2021, 04:13 PM
#2
Re: query help (JOIN?)
Sounds like a RIGHT OUTER Join, which would be a very strange animal indeed.
Take a look at the example here:
https://www.w3schools.com/sql/sql_join_right.asp
My usual boring signature: Nothing
-
Oct 25th, 2021, 09:45 PM
#3
Re: query help (JOIN?)
At first I thought maybe a FULL OUTER JOIN is what you'd need... but then I realized you wouldn't have a job w/o an employee... that doesn't make sense. So, yeah, a RIGHT JOIN might work. I'd probably write it as a LEFT JOIN starting with the Employee table then joining to the Jobs table, rather than the other way around. But it depends if you're writing this from scratch or having to bolt this onto something existing.
-tg
-
Oct 26th, 2021, 02:04 AM
#4
Re: query help (JOIN?)
Agree with tg: how can you have a job without an employee?
anyway: FULL OUTER JOIN would have been my choice here, but that depends on the Database (e.g. MySQL doesn't support FOJ)
Workaround might be a "m:m"-Setup with LEFT JOIN on jobs, and RIGHT JOIN on employees with the "connecting" table in the middle
To test everything some sample data would be nice
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Oct 27th, 2021, 11:33 PM
#5
Thread Starter
Frenzied Member
Re: query help (JOIN?)
Hi guys.
Thanks for the answers.
Actually, there will be jobs without employees and employees without jobs.
Hard to explain why but it has to do with vacancies and overhires.
I sometimes will have a record where there is just the employee information, and other times, a job that is currently unfilled with no employee information. But most records will have job and employee information.
Maybe I should call these "positions" instead of jobs to make it clearer.
I know I could do my usual left join, jobs to employees, and then run a second query for employees who do not show up in any of the job records, and then do the rest of the work in VBA, but I was just wondering if it were possible to get this in a single query in Access.
Wen Gang, Programmer
VB6, QB, HTML, ASP, VBScript, Visual C++, Java
-
Oct 28th, 2021, 01:11 AM
#6
Re: query help (JOIN?)
Should be possible.
Sample Data?
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Oct 28th, 2021, 07:43 AM
#7
Re: query help (JOIN?)
Originally Posted by wengang
Hi guys.
Thanks for the answers.
Actually, there will be jobs without employees and employees without jobs.
Hard to explain why but it has to do with vacancies and overhires.
I sometimes will have a record where there is just the employee information, and other times, a job that is currently unfilled with no employee information. But most records will have job and employee information.
Maybe I should call these "positions" instead of jobs to make it clearer.
I know I could do my usual left join, jobs to employees, and then run a second query for employees who do not show up in any of the job records, and then do the rest of the work in VBA, but I was just wondering if it were possible to get this in a single query in Access.
Then, yes, what you're looking for is a full outer join....
Code:
select *
from tblEmployee E
full outer join tblJob J on E.ID = J.EmployeeID
-- or --
select *
from tblJob J
full outer join tblEmployee E on J.Employee = E.ID
Since you'll be pulling from both tables the order doesn't really matter much. Fields where there isn't data will simply be null.
-tg
-
Nov 3rd, 2021, 07:40 PM
#8
Thread Starter
Frenzied Member
Re: query help (JOIN?)
Originally Posted by Zvoni
Agree with tg: how can you have a job without an employee?
anyway: FULL OUTER JOIN would have been my choice here, but that depends on the Database (e.g. MySQL doesn't support FOJ)
Workaround might be a "m:m"-Setup with LEFT JOIN on jobs, and RIGHT JOIN on employees with the "connecting" table in the middle
To test everything some sample data would be nice
Yes, sorry I didn't get back sooner. After some research, I found that Full Outer Join would have been the solution, but doesn't work in Access. Then I saw Left Join Union Right join as a workaround, and eventually built a workaround solution of multiple union statements, but with all left joins.
Would be cool to do the full join, but still, Access is surprisingly versatile all the same.
Wen Gang, Programmer
VB6, QB, HTML, ASP, VBScript, Visual C++, Java
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|