selecting from table A using WHERE criteria in table B

rebenbr · #1 (**permalink**) 06-25-10, 00:04

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!

shammat · #2 (**permalink**) 06-25-10, 02:23

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.

rebenbr · #3 (**permalink**) 06-25-10, 04:21

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

r937 · #4 (**permalink**) 06-25-10, 06:04

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'

dav1mo · #5 (**permalink**) 06-25-10, 08:28

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

rebenbr · #6 (**permalink**) 06-26-10, 02:38

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.

dav1mo · #7 (**permalink**) 06-29-10, 08:31

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

mikkom · #8 (**permalink**) 07-08-10, 02:40

Sorry, I saved message in wrong place...

Internet Services

Web Development

Digital Marketing

Consumer Tech