At 12:52 PM +0100 8/9/01, Dave Watkinson wrote:
>I have (many many many) tables ... two of which are linked by a third,
>so that there can be a one-to-many relationship. What I'd like to do is
>find which ids from table 1 have more than one relation in table 2, via
>table 3.
>I've tried this...
>SELECT  empid, COUNT(empid)  FROM emp_cont , employers, contacts WHERE
>emp_cont.empid = employers.uid and emp_cont.contid = contacts.uid 
>and count(empid) > 1;

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 like

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

Either statement provides SQL with implicit instructions, as to which 
empid to use and where.

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

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.

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

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]

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

$results=mysql_query (
SELECT empid, COUNT(empid) AS countid FROM emp_cont GROUP BY empid);

while ($row=mysql_fetch_array($results)){
        $countid        = $row["countid"];
        $empid  = $row["empid"];

        $display_employee .="";
        $display_employee .="
<? echo "$display_employee"; ?>


    Alnisa  Allgood
    Executive Director
    Nonprofit Tech
    (ph) 415.337.7412  (fx) 415.337.7927
    Nonprofit Tech E-Update
    mailto:[EMAIL PROTECTED]
    applying technology to transform

PHP Database Mailing List (
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to