Where... In (Select) question 1

[Nevermoor]

I have two databases

"Employees" has names and ids, "Events" lists specific events and has a column that can contain employee ids.

I want to select a list of employees from the "Employees" table limited to only those who have been associated with an event.

I thought the syntax was "SELECT * FROM employees WHERE empID IN (SELECT empID FROM events)" but that gives me no results.

Any suggestions would be much appreciated.

Thank you,
-Nm

 

[IanWaterman]

A better query would be

Select E.* from Employees E
inner join Events ev on e.empID = ev.empID

This assumes data types for empID are same for both tables.

Ian

 

[r937]

if employees-to-events is one-to-many, then you'll want each employee only once who has any events

SELECT * 
  FROM Employees 
 WHERE empID IN 
       ( SELECT empID FROM Events )

alternatively --

SELECT Employees.* 
  FROM Employees 
INNER
  JOIN ( SELECT DISTINCT empID 
           FROM Events ) AS de
    ON de.empID = Employees.empID

this also works but is inefficient --

SELECT DISTINCT e.* 
  FROM Employees AS e
INNER
  JOIN Events AS ev
    ON ev.empID = e.empID

finally, this non-standard use of GROUP BY works in mysql only bu may actually be efficient --

SELECT e.* 
  FROM Employees AS e
INNER
  JOIN Events AS ev
    ON ev.empID = e.empID
GROUP
    BY e.empID

r937.com | rudy.ca

 

[Nevermoor]

Thanks r937.

For whatever reason those first two options won't work, but the third one did. It's a small enough database (~1000 employees) that I can't imagine the inefficiencies are worth worrying about.

 

[r937]

what does "won't work" mean? of course they will work

r937.com | rudy.ca

 

[Nevermoor]

It means they return 0 results (unlike the 3rd one, which works as expected).

 

[r937]

what version of mysql are you on?

r937.com | rudy.ca