MySQL Query Help

Hi All,

   I need some help with a MySQL query.  I have 2 databases - one contains employee names and whether they are active or not.  The other one contains a list of codes/descriptions.

The table ee contains name, code and active
The table eereminders contains code.

I need a query to display which ee.active = 'Y' and which `name` are contained in the eereminders table and which ones aren't.   They are linked via ee.code and eereminders.code

I'm guessing it will have to be 2 queries...

Thanks!
Zack
LVL 4
zack4x4Asked:
Who is Participating?
 
bklyn2001Connect With a Mentor Commented:
this query will show which entries are in eereminders:

SELECT ee.Name
FROM ee
INNER JOIN eereminders
  ON ee.code = eereminders.code
WHERE ee.active = 'Y'

Open in new window

0
 
zack4x4Author Commented:
That worked for the first part and I will give u the points for it but I was wondering if you could help me with the query that shows all the employee names that don't have a specific code?

Thanks!!
Zack
0
 
SharathData EngineerCommented:
>> I need a query to display which ee.active = 'Y' and which `name` are contained in the eereminders table
SELECT ee.Name 
  FROM ee 
       INNER JOIN eereminders 
         ON ee.code = eereminders.code 
 WHERE ee.active = 'Y'

Open in new window

>> I need a query to display which ee.active = 'Y' and which `name` are notcontained in the eereminders table
SELECT ee.Name 
  FROM ee 
       LEFT JOIN eereminders 
         ON ee.code = eereminders.code 
 WHERE ee.active = 'Y' 
       AND eereminders.code IS NULL

Open in new window

>> query that shows all the employee names that don't have a specific code. you mean this?
SELECT * 
  FROM ee 
 WHERE code IS NULL

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
bklyn2001Commented:
think this should work:
SELECT ee.Name, eereminders.id
FROM ee
LEFT JOIN eereminders
  ON ee.code = eereminders.code
WHERE eereminders.code IS NULL
  AND ee.active = 'Y'

Open in new window

dunno if you're looking for active employees, but you can exclude the last line if not.
0
 
zack4x4Author Commented:
Sharath the 2nd and 3rd example didn't work and bklyn example didn't work, but the 1st one did you sent.  I tried it and I apologize I gave the wrong fields...  I'm sorry I will give a more clarified example with the correct names.

The `name` from table ee shows all the names in the DB.  When I search table eereminders for code = 'PDL' I need to display all the names (from table ee) that don't have the eereminders.code = 'PDL' and ee.active = 'Y'   The field ID is common between tables ee and eereminders.  Code is a column only in eereminders.  Sorry for the confusion earlier!

Thanks - Zack
0
 
bklyn2001Commented:
I was playing around with temp tables and forgot to change all of the variable names back...

SELECT ee.Name
FROM ee
LEFT JOIN eereminders
  ON ee.id = eereminders.id
WHERE eereminders.id IS NULL
  AND ee.active = 'Y'
  AND eereminders.code = 'PDL'

Open in new window

0
 
SharathData EngineerCommented:
Can you provide some sample data with expected result
0
 
zack4x4Author Commented:
In table ee here is the db structure
Table ee

========

id, name, fname, lname, active, hired, termdate, emergname, emergrelations, emergnumber, sinnum, username, pwd, epwd, lastpwdchange, role, location

--------

id               int(11) PK

name             varchar(45)

fname            varchar(45)

lname            varchar(45)

active           varchar(1)

Data Example:
id: 1
name: joe smith
id: 2
name: jane smith

in eereminders the structure is

Table eereminders

=================

record, id, code, lastcompdate, expires, reminder, reminded, initreminder, finalreminder, remindedinit, remindedfinal

-----------------

record           int(11) PK

id               int(11)

code             varchar(12)


Data example in EEReminders
record 1
id: 1
code: TEST
record 2
id: 1
code: TEST1
record 3
id: 2
code: TEST1

need a query to pull for instance if I want to see who doesn't have the code TEST1 it would display that id 1 which has a name of Joe Smith.

Hope that helps.  Thanks!!

Thanks!! Zack
0
 
SharathConnect With a Mentor Data EngineerCommented:
You mean this?
SELECT * 
  FROM ee 
 WHERE id IN (SELECT id 
                FROM EEReminders 
               WHERE code <> 'TEST1')

Open in new window

0
 
zack4x4Author Commented:
Sharath that didn't quite work with  the data in my DB.  It kept bringing up all the records in the EE.  I'm going to close off the question and give bklyn and sharath both points for your help! It's appreciated!  I'm going to go about it in a different way.

Thanks again!
Zack
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.