Query Criteria Help - Multiple Values

Question

Hi Folks - I have a list of students that can have multiple codes assigned:

Sample Data:

ID,Code

12345, ABCD

12345, DDDD

12345, FFFF 

12349, ABCD

12349, DDDD

12349, TTTT

I'd like to develop a query that displays students that have CODE X AND CODE Y, So, using the sample data, what would my criteria look like if I wanted to display all students that have CODE = ABCD AND CODE = DDDD?

Note: If they have only ABCD, they should not be in the list. If they have only DDDD, they should not be in the list.

Thanks.

Michael

Answer 1

Have you tried my SQL/Query?

Answer 2

Try something along these lines:

SELECT ID

FROM StudentCodes AS SC1

WHERE Code = "ABCD"

AND EXISTS

(SELECT *

FROM StudentCodes As SC2

WHERE SC2.ID = SC1.ID

AND Code = "DDDD");

The result table would be of limited use of course.  I assume that you also have a referenced Students table, in which case a more meaningful result table would be returned by:

SELECT Students.*

FROM StudentCodes AS SC1

INNER JOIN Students

ON Students.ID = SC1.ID

WHERE Code = "ABCD"

AND EXISTS

(SELECT *

FROM StudentCodes As SC2

WHERE SC2.ID = SC1.ID

AND Code = "DDDD");

However, I'd recommend that you do not use the generic ID as a column name.  Always use names which specifically describe the attribute represented by the column, e.g. StudentID.

Answer 3

Hi - The value of CODE cannot be "ABCD" and "DDDD" simultaneously, so that would never return results. Also, I believe the IN function uses OR logic. If that's true, then the result set could have "ABCD" without "DDDD" and vice versus.

Thanks,

Michael

Answer 4

For some reason I'm thinking I'm overlooking something simpler, but at first glance I'd do something like:

SELECT DISTINCT T_Students.ID

FROM (T_Students INNER JOIN (

   SELECT T_Students.ID

   FROM T_Students

   WHERE (T_Students.Code="ABCD")

) AS Tmp1 ON T_Students.ID = Tmp1.ID) INNER JOIN (

   SELECT T_Students.ID

   FROM T_Students

   WHERE (T_Students.Code="DDDD")

) AS Tmp2 ON T_Students.ID = Tmp2.ID;

Where T_Students would be the name of your table.

Answer 5

You should be able to use

WHERE Code = "ABCD" AND CODE = "DDDD"

or

WHERE Code IN("ABCD", "DDDD")