Query to find records not linked in another table

MySQL database
#1

Hi!
For this problem, I’m using three tables:
Employees (id, lastName, firstName, etc.)
Groups (id, group, description)
GroupsLink (id, empID, groupID)

I need to be able to display all employees of a particular group based on groupID. This works great:

$sql = "SELECT Employees.lastName , Employees.firstName, Employees.id FROM Employees , GroupsLink WHERE ( ( GroupsLink.groupID = $id ) AND (GroupsLink.empID = Employees.id ) )";
But next I want to display all the employees that are NOT in that group. I could probably do this with php by setting two arrays and comparing, but it sure seems like there must be an easier/more efficient way to do this with a query. I’ve tried different things after Google searching, but I either end up with nothing or multiple duplicates.

Anyone got ideas or suggestions?
(MySQL Version 5.0.45; PHP Version 5.2.17)

#2

Always faster to use a query than parse thru tables with PHP…

Just use the NOT option… (where…<>… meaning groupID is NOT whatever…)

$sql = "SELECT Employees.lastName , Employees.firstName, Employees.id
      FROM Employees , GroupsLink
        WHERE (
            ( GroupsLink.groupID <> $id ) AND (GroupsLink.empID = Employees.id )
            )";

Hope that helps…

#3

Thanks for the speedy response!
But that didn’t work. I need to sort/limit on groupID, so instead I tried:

$sql = "SELECT Employees.lastName , Employees.firstName, Employees.id FROM Employees , GroupsLink WHERE ( ( GroupsLink.groupID = $id ) AND (GroupsLink.empID <> Employees.id ) )";
But that returns multiple duplicate results. I’m probably overlooking some small detail, but my brain is to fried to figure it out…

#4

For the unique, use the unique SQL option…
Read here: http://www.w3schools.com/sql/sql_unique.asp

For the order or sort, use the order-by SQL option…
Read here: http://www.w3schools.com/sql/sql_orderby.asp

That should help…

#5

If I understand the unique property correctly, I can’t use it for the GroupsLink table because the fields empID and groupID hold multiple similar values as they link employees ids to group ids. If that makes sense.

I’m also trying a subquery, but am probably not using that correctly either:

SELECT Employees.lastName , Employees.firstName, Employees.id
FROM Employees
WHERE Employees.id NOT EXISTS
      
(
SELECT Employees.id
FROM Employees , GroupsLink
WHERE (
            ( GroupsLink.groupID = 1 ) AND (GroupsLink.empID = Employees.id )
       )
)

This just returns an error:
"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘EXISTS ( SELECT Employees . id FROM Employees , GroupsLink WHERE ( ( Group’ at line 1 "

#6

I have to leave for a couple hours or maybe more, but, post in english what you want to pull from the DB.
I will solve it for you when I get back. It does not need a sub-query. Just the correct order of where’s…

Was it like this:
you want
ALL Employees where GroupsLink.groupID NOT EQUAL to $id
AND GroupsLink.empID NOT EQUAL to Employees.id

Let me know and I will help you in a few hours…

#7

Thanks, I really appreciate the help! No rush, please just when you have spare time! I’m not on a time crunch, just got tired of failing on my own :wink:

In simple terms, I want to pull all employees from the table Employees who are not a part of a specific group.
Employees are identified uniquely by Employees.id
Groups are managed by table ‘Groups’, which are identified uniquely by Groups.id
The two are linked by table ‘GroupsLink’ which only consists of
id (key auto number)
empID linked to Employees.id
groupID linked to Groups.id

So, I want to look at GroupsLink and find all employees that DONT have an entry to a specific groupID.
Simple statement is:

SELECT Employees.id where GroupsLink.groupID = $id (Group.id) AND Employees.id <> GroupLinks.empID

Hope that makes sense.
Thanks again!

#8

So, let’s see, you want to pull employees that are NOT members of a certain group.

Groups are defined as in table ‘Groups’. Each ‘Group’ has a ‘Groups.id’.

So, wouldn’t you just use that only? Something like this:

$query=“SELECT Employees.id FROM Employees WHERE Employees.id <> GroupsLink.empID”;

If this is not what you want, can you explain to me how you define how an employee is placed into a group?
What deifnes an employee in a group? I never use linked tables as they always confuse me. Can an employee be in more than one group? Are they just in one group? If so, where in the groups table are they defined as in that group? I will check in often, just have to run out to the store for some food later on… We can figure this out…

#9

Yes, I think you understand what I’m after, and it seems like the code you provided should work. But it doesn’t. For testing purposes, this is the code I am using:

SELECT Employees.lastName , Employees.firstName, Employees.id
FROM Employees , GroupsLink 
WHERE 
( 
   ( GroupsLink.groupID = 1 ) AND (GroupsLink.empID <> Employees.id )
)

I thought this would first limit results to records with a groupID=1
Then select all employees who are not part of ‘group 1’ based on the contents of GroupsLink.

Instead, it seems to be returning results of EVERY employee based on every record in GroupsLink, and NOT just limiting to groupID=1. I’m soo confused :-[

Here is some sample data, maybe that will help you see what I’m doing wrong:

Employees (id, lastName)
1, Smith ; 2, Jones

Groups (id, group)
1, Group1; 2, Group2

GroupsLink (id, empID, groupID)
1, 1, 1;    2, 2, 1;     3, 1, 2

So Smith is in Group1 and Group2, while Jones is only in Group1

I built the tables this way on to be ‘normalized’. And again, it works beautifully for displaying employees in a group.

Thanks again, after reading other posts in this forum it looks like you have your hands full, so I really appreciate the help!

#10

As a side note, the purpose of this will be to display all employees in a group in one column, and all other employees NOT in the group in another column. This will be in a form with check boxes so employees can be selected and added to or deleted from the group.

Seemed like this would be a user friendly way to manage employees in the groups.

#11

Yes, I have been helping a lot of people this week… LOL, but, programming puzzles are fun to some…

Well, thanks for you data breakdown. It helps me visualize what you are working with. I am looking at it now and perhaps I can figure it out for you. I think it is the way you group the id’s into groups.

It looks like you have to do an inner select of the groups that the employee is in first. Since they are in more than one group, the way I first suggested will not work. Give me a little time and I will suggest another way to try it all… (busy busy busy… LOL…)

#12

I agree, it’s exactly like working a puzzle. And usually is fun, until you get stuck on one piece for days lol.

I’ll read up and refresh on inner joins so I will be able to understand what you come up with, and perhaps figure it out myself.

:slight_smile:

#13

YIKES! I thought I had the solution and posted it at the end of the last post, but, it did not go thru.
I will retype it all and send it… Don’t go away… LOL…

#14

Okay, let’s see… What did I type last note that didn’t post…

You need to select employees.id’s from a group if they do NOT EXIST in the group…

So, I found an SQL clause that is important for you to learn. It basically will allow you so select data from a group of data if it does NOT exist inside the data. So, I think that is exactly what you are looking for.

Here is the link to explanation of the “NOT EXISTS” clause:
http://www.techonthenet.com/sql/exists.php LOOK at example#2…

So, using that example, this might be the new query for you:

OLD query:
SELECT Employees.lastName , Employees.firstName, Employees.id FROM Employees , GroupsLink
WHERE ( ( GroupsLink.groupID = 1 ) AND (GroupsLink.empID <> Employees.id ) )

NEW query:
SELECT Employees.lastName , Employees.firstName, Employees.id FROM Employees WHERE Employees.id NOT EXISTS (SELECT Employees.id FROM GroupsLink.empID)

Or something like this… You will have to test this and see what it pulls. I do not have your database to access, so I can not test it. I would suggest just doing this: Set up a page to pull all data based on a query and display it. Nothing to do with your other stuff. Simply pull data from your database using the NEW query above. Display and see if it is what you want. Use “Smith” or whatever for your test data to query from. Once you get the correct data displayed, then, use this query in the live pages…
Hope all that makes sense… Let me know if it works or NOT…

#15

Thanks, I’ll get into it and let you know!

#16

**** Update *****
Have not gotten this to work yet, but have learned a few things, I think.
First, I’m using phpMyAdmin to run queries which makes things easier.
Came up with this code:

SELECT Employees.id, Employees.lastName FROM Employees, GroupsLink WHERE NOT EXISTS (SELECT * FROM Employees, GroupsLink WHERE (GroupsLink.groupID=1) AND (Employees.id = GroupsLink.empID) )
Which returns empty. If I understand NOT EXISTS it returns empty because the subquery DOES return results, as long as there are some employees assigned to that particular group.

Also, using Employees.id <> GroupLinks.empIDReturned multiple results because it returns as true for every employee who’s id is not equal to each record on the GroupsLink table.

With that said, I’m starting to think maybe I should approach this issue from a different angle. Like I said in the beginning, I could create two arrays in php: one with all employees, and one with employees in a specific group, then compare and eliminate the duplicates to produce an array containing those employees who are not in the group. Not as efficient, but it should work.

Not sure how much time I will have to devote to this today, as I will be in and out all day. Any other thoughts or suggestions (as you have time)?

Thanks again for all your assistance.
And I will update with whatever solution I finally come up with!

#17

Sometimes you just got to walk away for a bit…
Just sat down and in 5 minutes came up with this:

SELECT Employees.id, Employees.lastName FROM Employees WHERE NOT EXISTS (SELECT * FROM GroupsLink WHERE (Employees.id = GroupsLink.empID) AND (GroupsLink.groupID = 1))
… and it works!
;D

#18

Very glad you have a solution. It is ALWAYS a warm and fuzzy feeling when you solve a programming project problem. Hopefully, this will help someone else, too. CYA in the bitstream…

#19

Yeah, I hate it when someone posts they solved their issue, but don’t mention how!

Thanks again for the help!

Sponsor our Newsletter | Privacy Policy | Terms of Service