Results 1 to 8 of 8

Thread: query help (JOIN?)

  1. #1

    Thread Starter
    Frenzied Member wengang's Avatar
    Join Date
    Mar 2000
    Location
    Beijing, China
    Posts
    1,568

    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

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    36,747

    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

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,526

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,721

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

  5. #5

    Thread Starter
    Frenzied Member wengang's Avatar
    Join Date
    Mar 2000
    Location
    Beijing, China
    Posts
    1,568

    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

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,721

    Re: query help (JOIN?)

    Should be possible.
    Sample Data?
    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

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,526

    Re: query help (JOIN?)

    Quote Originally Posted by wengang View Post
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Frenzied Member wengang's Avatar
    Join Date
    Mar 2000
    Location
    Beijing, China
    Posts
    1,568

    Re: query help (JOIN?)

    Quote Originally Posted by Zvoni View Post
    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
  •  



Click Here to Expand Forum to Full Width