thanks for that ... I've made some comments within yours...


Your getting a lot of errors, because the statement has well a lot of
mistakes. Let's start near the beginning, you state:

SELECT  empid, COUNT(empid)  FROM emp_cont , employers, contacts

This statement should produce an error, since you didn't tell the
database which table to take empid from.  A working statement would look
[Dave Watkinson] 
yeah, but empid inly exists in one of the tables in the FROM argument,
so there's no error there 

SELECT  empid, COUNT(empid)  FROM emp_cont   or

SELECT  emp_cont.empid, COUNT( emp_cont.empid)  FROM emp_cont ,
employers, contacts

The next major error is to use a Count function without the group by.
I'm not an SQL guru, but I believe whenever you use count you must also
use group by or having so adding a

SELECT  empid, COUNT(empid)  FROM emp_cont GROUP BY empid
[Dave Watkinson] 
yep - that works :-) 

will correct that.

Unfortunately, I think the larger issue is how your trying to find your
query.  Your query did confuse me a little, so I could be wrong, but
technically speaking, if you are trying to locate an employee with more
than one record in employee contracts, then there's no need to involve
the contracts table within the query.
[Dave Watkinson] 
no - it's employers with more than one contact, hence the names of those
tables in the WHERE bit :-)

So say I have table Employees that contains empid, name, ssn.  Then I
have table Employee Contracts that contains, empid, contractid.
Assuming that all empid(s) on Employee Contracts have a correlating
empid in Employees (which they will if the table was defined properly)
then the steps to get what you want are:

Step 1:  Return a Count of empid

SELECT COUNT(empid) AS countid FROM emp_cont GROUP BY empid

Step 2: Return employees with more than one contract

SELECT empid FROM emp_cont WHERE countid >1

[Dave Watkinson] 
makes sense 

You'll note that both step requires a return, which general means a
subquery, I believe for MySQL to hanle subqueries you actually have to
perform to separate queries. If your not  using MySQL then you could
write a statement such as:

SELECT empid FROM emp_cont WHERE (SELECT COUNT(empid) FROM emp_cont
GROUP BY empid) [note statement may need tweaking since I use MySQL and
can't test it]

that didn't work

If your using MySQL, I believe you need to return the results from one,
then use the results to find the next. Or you could use PHP to cheat,
and return all results, but only display the ones where the count is
greater than 1.

A subquery is best if your tables are large, returning 1000 results to
display 50 wastes time. But if you have under 200 employees, then just
use the SQL statement
[Dave Watkinson] 
I *am* using MySQL, but found another way round it - Steve got it right
away - I wanted a one-to-many, not a many-to-many, so I just added an
employer_id column to the contacts table.
Thanks anyway .. I'm getting used to GROUP now :-)

   Alnisa  Allgood
   Executive Director
   Nonprofit Tech
   (ph) 415.337.7412  (fx) 415.337.7927
   Nonprofit Tech E-Update
   applying technology to transform

Reply via email to