[PHP-DB] Another SELECTING problem :-(
I've a feeling I've asked this before, but checked my old messages and couldn't see it in there for the excess of non PHP-Database questions flying around (yep - that's a joke, but I've been here a while - forgive me!). 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; and get all kinds of invalid grouping messages many TIA Dave
RE: [PHP-DB] Another SELECTING problem :-(
PS This is for a web page, so it's PHP related!!! :-) -Original Message- From: Dave Watkinson Sent: 09 August 2001 12:51 To: PHP-DB List (E-mail) Subject: [PHP-DB] Another SELECTING problem :-( I've a feeling I've asked this before, but checked my old messages and couldn't see it in there for the excess of non PHP-Database questions flying around (yep - that's a joke, but I've been here a while - forgive me!). 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; and get all kinds of invalid grouping messages many TIA Dave -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Another SELECTING problem :-(
Dave Watkinson [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... PS This is for a web page, so it's PHP related!!! :-) -Original Message- From: Dave Watkinson Sent: 09 August 2001 12:51 To: PHP-DB List (E-mail) Subject: [PHP-DB] Another SELECTING problem :-( I've a feeling I've asked this before, but checked my old messages and couldn't see it in there for the excess of non PHP-Database questions flying around (yep - that's a joke, but I've been here a while - forgive me!). I have (many many many) tables ... two of which are linked by a third, so that there can be a one-to-many relationship. three tables defines a many to many relationship. a one to many is defined by posting a foreign key. try it with 2 tables. 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; and get all kinds of invalid grouping messages many TIA Dave -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Another SELECTING problem :-(
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];
if($countid==1)
{
$display_employee .=;
}
else
{
$display_employee .=
p$empid/p;
};
}
?
? echo $display_employee; ?
Alnisa
--
.
Alnisa Allgood
Executive Director
Nonprofit Tech
(ph) 415.337.7412 (fx) 415.337.7927
(url) http://www.nonprofit-techworld.org
(url) http://www.nonprofit-tech.org
(url) http://www.tech-library.org
.
Nonprofit Tech E-Update
mailto:[EMAIL PROTECTED]
.
applying technology to transform
.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Another SELECTING problem :-(
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 like [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. D'oh! Thanks anyway .. I'm getting used to GROUP now :-) Cheers Dave Alnisa -- . Alnisa Allgood Executive Director Nonprofit Tech (ph) 415.337.7412 (fx) 415.337.7927 (url) http://www.nonprofit-techworld.org (url) http://www.nonprofit-tech.org (url) http://www.tech-library.org . Nonprofit Tech E-Update mailto:[EMAIL PROTECTED] . applying technology to transform .
RE: [PHP-DB] Another SELECTING problem :-(
At 3:10 PM +0100 8/9/01, Dave Watkinson wrote: 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 Yeah, that one shouldn't work on MySQL. MySQL doesn't allow subqueries like this. But here's another option you could use: SELECT empid, COUNT(empid) FROM emp_cont GROUP BY empid HAVING COUNT(empid) 1 I tend to use either group by or having, so forgot that having can be used to limit a group by. But this statement should work for you, as well. Alnisa -- . Alnisa Allgood Executive Director Nonprofit Tech (ph) 415.337.7412 (fx) 415.337.7927 (url) http://www.nonprofit-techworld.org (url) http://www.nonprofit-tech.org (url) http://www.tech-library.org . Nonprofit Tech E-Update mailto:[EMAIL PROTECTED] . applying technology to transform .
RE: [PHP-DB] Another SELECTING problem :-(
Another possible solution (although more drastic) is to switch DBMS's. I use PostgreSQL with no troubles. However, 6.5 and earlier are not so great performance wise. 7.0.3 is OK, and 7.1.2 is exceptional. It also now (finally) supports outer joins and write ahead locking. If you are at the beginning of a project, this might not be a big switch. If you have a lot of data already loaded, this would be an unpleasant solution. __ Your mouse has moved. You must restart Windows for your changes to take effect. #!/usr/bin/perl print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10); -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
