Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2010
    Posts
    3

    Unanswered: selecting from table A using WHERE criteria in table B

    I know this has to be very simple, but I just can't seem to make it work. I need to select data from employee event records in one table, but only for active employees. Each record shows the employees status at the time it was created, but the employees current status is stored in another table.

    To put it a little more simply: I want to pull employee records from one table, but only records for current employees. Employees current status's are in a separate table. I don't need any data from the status table; I just want to use it to filter my search results.

    I have tried a few joins, and feel like the issue I'm having is with syntax, so I'm going to state what i'm looking for and hopefully someone can tell me how to write it so it will work:

    select miscrows
    from table A
    where table A employee status = Active
    and table B employee status = Active


    Of course, the employee status columns have different names in each table, so I would need to make sure that part is formatted correctly as well.

    PLEASE HELP!
    Last edited by rebenbr; 06-25-10 at 01:14.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Post the complete deifnition (ideally as CREATE TABLE) of the two tables.
    Post sample data (ideally as INSERT INTO) for the two tables
    Show the expected output.

  3. #3
    Join Date
    Jun 2010
    Posts
    3
    the tables have several columns in them, so i will list the ones i'm interested in. please let me know if this is what you're looking for.

    Table A details

    Name:
    Personnel_Calendar

    Columns:
    Entry_Date
    Employee_Name
    Description
    Work_Code
    Comments
    Status


    Table B details

    Name:
    Employee_Information

    Columns:
    Employee_Name
    Employee_Status

    Sample data:
    Personnel_Calendar
    Entry_Date Employee_Name Description Work_Code Comments Status
    6/25/2010 Smith, John Vacation VA Out of town Active
    6/20/2010 Doe, Jane Vacation VA on call Active
    5/15/2010 Doe, Jane Sick SI doctors note Active



    Employee_Information
    Employee_Name Employee_Status
    Smith, John Active
    Doe, Jane Inactive



    Expected results:
    Entry_Date Employee_Name Description Work_Code Comments
    6/25/2010 Smith, John Vacation VA Out of town
    Last edited by rebenbr; 06-25-10 at 05:47.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT i.Entry_Date
         , i.Employee_Name
         , i.Description
         , i.Work_Code
         , i.Comments
      FROM Employee_Information AS i
    INNER
      JOIN Personnel_Calendar AS c
        ON c.Employee_Name = i.Employee_Name
     WHERE i.Employee_Status = 'Active'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Slight switch around to Rudy's SQL, as has the incorrect correlation name.
    Code:
    SELECT c.Entry_Date
         , c.Employee_Name
         , c.Description
         , c.Work_Code
         , c.Comments
      FROM Employee_Information AS i
    INNER
      JOIN Personnel_Calendar AS c
        ON c.Employee_Name = i.Employee_Name
    --your original query was, also, looking for status in this table to be active,
    --but you did not describe that, so if you need it, uncomment the next line
    --   AND c.status = 'Active'
     WHERE i.Employee_Status = 'Active'
    Dave

  6. #6
    Join Date
    Jun 2010
    Posts
    3
    Well, these queries don't fail, but they don't return any results either. Not sure what the issue is, but I think I have found a work around. I am pulling in data from both tables into seperate recordsets, and then reconciling them on the client side. It's not pretty, but it seems to be working. Thanks to everyone for your help.

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    hopefully, your application stays as very low volume as possible. To paraphrase Bonnie Baker(a noted DB2 Consultant), you are making this into a stage 3 prediate, which is the worst performing type of work you can do.
    Also, for the description you gave they do work. If they are not working then you need to give better description of your issue/needs.
    Dave

  8. #8
    Join Date
    Mar 2010
    Posts
    4
    Sorry, I saved message in wrong place...
    Last edited by mikkom; 07-08-10 at 03:48.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •