Tricky (for me) SQL Query using COUNT

[acdougla17]

List all columns in Employee that have more than 2 corresponding rows in Assignment.

The columns they have in common are Employee.id and Assignment.eid.


My method of thinking for this is to look for each time id = eid and the count the number of times it appears in table Assignment. I have tried at least 5 different times and cant get anything working. I am pretty new to SQL and I think I am having a hard time with certain questions because I approach like I would in a programming class.


Here are a couple of my recent attempts.

SELECT *
FROM Employee WHERE EXISTS (SELECT eid FROM Assignment WHERE id = eid AND (SELECT COUNT(eid) FROM Assignment HAVING Count(eid) > 2));

SELECT * FROM Employee WHERE EXISTS (SELECT eid, COUNT(eid) FROM Assignment WHERE id = eid AND HAVING COUNT(eid) > 2);

[Robeen]

Try something like this - I just got it to work on a couple of tables I rigged up just for this query.

The two Tables [Table1 & Table2] have Fields named 'One', 'Two', 'Three' . . . [I know . . . corny . . . ]!

It works for me.

 
SELECT Table1.One, Count(Table2.Two) AS NumberOfRecs
FROM Table1 LEFT JOIN Table2 ON Table1.One = Table2.One
GROUP BY Table1.One
HAVING (((Count(Table2.Two))>4))
ORDER BY Table1.One;

I hope this helps!