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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
SharathData 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.