List ID's with multiple values in same column (1 Viewer)

russmann2000

Registered User.
Local time
Yesterday, 21:09
Joined
Mar 25, 2018
Messages
13
I have a table with EMPLOYEE_IDs and a column (EMPLOYEE_CODE) with more than one value for the same EMPLOYEE_ID. I'm attempting to pull all of the ID's with more than one EMPLOYEE_CODE, can get it to list the ID's, but if I try to add the CODE I get nothing. This is what I have if I just want the ID.

SELECT EMPLOYEE_ID
FROM tblEMPLOYEE
WHERE EMPLOYEE_CODE In ("CODE 1","CODE 2")
GROUP BY EMPLOYEE_ID
HAVING Count(EMPLOYEE_ID) >1

How can I get it to list the EMPLOYEE_CODEs as well?
 
Last edited:

plog

Banishment Pending
Local time
Yesterday, 21:09
Joined
May 11, 2011
Messages
11,613
I don't think that SQL works. For every field in the SELECT it must either be in the GROUP BY or be contained within an aggregate function (SUM, COUNT, MAX, etc) in the SELECT. You have one field in your SELECT and another in the GROUP BY. That should not work.

Now, I understand your issue, but I don't understand what you ultimately want. Could you please demonstrate it with data? Let's use the below data set as what's in your table:

tblEmployee
EMPLOYEE_ID, EMPLOYEE_CODE
1, CODE 1
1, CODE 2
1, CODE 3
2, CODE 1
3, CODE 3
3, CODE 4
4, CODE 1
4, CODE 3
5, CODE 1
5, CODE 1

Using that data, show me what data you expect the query to return. No need for any explanation, just show me what data you expect.
 

russmann2000

Registered User.
Local time
Yesterday, 21:09
Joined
Mar 25, 2018
Messages
13
I edited. I want the ID and the code.

I want all of the IDs with both CODE 1 and CODE 2.

1, Code 1
1, Code 2

And so on.

If I do like the code on the original post it lists all of the IDs just fine, but if I try to get it to list the codes as well, I get nothing.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:09
Joined
Oct 29, 2018
Messages
21,358
I edited. I want the ID and the code.

I want all of the IDs with both CODE 1 and CODE 2.

1, Code 1
1, Code 2

And so on.

If I do like the code on the original post it lists all of the IDs just fine, but if I try to get it to list the codes as well, I get nothing.
Hi. This is very possible but it's not quite clear what your goal is. Can you please post more data than what you already showed us? In your original SQL/query, you said it accurately gives you the IDs with both Code1 and Code2 but couldn't list the codes with the ID. However, we know those IDs have both Code1 and Code2, so there's really no need to list them, is there? However, if you're saying an ID with both Code1 and Code2 could also have Code3 and you want to see it listed, then that's something you have to tell us. In the end, whatever it is you're trying to do, I'm pretty sure we'll be able to show you how to do it. We'll just need to make sure we understand what it is first.
 

russmann2000

Registered User.
Local time
Yesterday, 21:09
Joined
Mar 25, 2018
Messages
13
I want to list both the ID, and the CODE. It’s possible to have more codes, but I only want CODE 1 and CODE 2.

If I use the sql in the original post, it gives me just the ID (obviously). I know it’s correct because I already know which IDs have which codes.

It’s obviously necessary, I wouldn't ask otherwise.

I’ve tried to get it to display the Codes as well but I get 0 records.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:09
Joined
Feb 19, 2002
Messages
42,981
You can use the query wizard to build a find duplicates query. That may be what you want.
 

June7

AWF VIP
Local time
Yesterday, 18:09
Joined
Mar 9, 2014
Messages
5,423
Try:

SELECT tblEmployee.* FROM tblEMPLOYEE
WHERE EMPLOYEE_CODE IN("CODE 1", "CODE 2") AND EMPLOYEE_ID IN (
SELECT EMPLOYEE_ID
FROM tblEMPLOYEE
WHERE EMPLOYEE_CODE In ("CODE 1","CODE 2")
GROUP BY EMPLOYEE_ID
HAVING Count(*) >1);

Or try INNER JOIN your first query to tblEmployee.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:09
Joined
Oct 29, 2018
Messages
21,358
I want to list both the ID, and the CODE. It’s possible to have more codes, but I only want CODE 1 and CODE 2.

If I use the sql in the original post, it gives me just the ID (obviously). I know it’s correct because I already know which IDs have which codes.

It’s obviously necessary, I wouldn't ask otherwise.

I’ve tried to get it to display the Codes as well but I get 0 records.
Hi. Thank you for the clarification. We know it is necessary or you wouldn't be asking for it. We just couldn't understand what it was you wanted. Now that you've given us more information, please try June7's suggestion and let us know how it goes. Cheers!
 

russmann2000

Registered User.
Local time
Yesterday, 21:09
Joined
Mar 25, 2018
Messages
13
Thanks so much. That solved it.

I tried a similar subquery earlier, just didn't do it correctly.

I need to step away from the code sometimes, this was way simple and should have caught it. :banghead:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:09
Joined
May 7, 2009
Messages
19,169
much simpler:
Code:
SELECT employees.Employee_ID, employees.Employee_Code
FROM employees
GROUP BY employees.Employee_ID, employees.Employee_Code
HAVING (((employees.Employee_Code)<"Code 3") AND ((Count("1"))<3))
ORDER BY employees.Employee_ID, employees.Employee_Code;
 

Users who are viewing this thread

Top Bottom