RE: [PHP-DB] Can't get left join to work
Actually, the && is the same as "and". So it could be written as: > mysql> select e.id,fname,sum(m.miles) from employees e > -> left join mileage m on m.id=e.id and month(trip_date)=3 > -> and substring(year(m.trip_date),3,2) = '02' > -> group by e.id; I'm just lazy...easier to hit & twice than type out "and". You were on the right path. You simply needed to move the conditional statements from the WHERE to the JOIN clause. I learned that stuff from reading the comments/answers on the list from DuBoise, Zawodny, and others. -Original Message- From: paul wilczynski [mailto:[EMAIL PROTECTED]] Sent: Friday, February 08, 2002 5:42 AM To: Rick Emery Subject: Re: [PHP-DB] Can't get left join to work Thank you! I'm obviously going to have to read up on that 'on ... &&' syntax - it's not something I'm familiar with. Paul Rick Emery wrote: > And the answer is: > > mysql> select e.id,fname,sum(m.miles) from employees e > -> left join mileage m on m.id=e.id && month(trip_date)=3 > -> && substring(year(m.trip_date),3,2) = '02' > -> group by e.id; -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Can't get left join to work
And the answer is: mysql> select e.id,fname,sum(m.miles) from employees e -> left join mileage m on m.id=e.id && month(trip_date)=3 -> && substring(year(m.trip_date),3,2) = '02' -> group by e.id; yeilds: +---++--+ | id| fname | sum(m.miles) | +---++--+ | 021021021 | Sam| 0.00 | | 121212121 | George | 0.00 | | 121212122 | George C. | 0.00 | | 121212123 | George | 0.00 | | 123123124 | Jake | 0.00 | | 123412342 | Paul | 0.00 | | 138501999 | Sherman| 0.00 | | 2 | Sam| 0.00 | | 3 | Fred Robert| 0.00 | | 4 | Paul | 0.00 | | 893458009 | Joan H | 0.00 | | 6902D | Ian| 1372.00 | | ABCABCABC | Ian|80.00 | | B | Bob| 0.00 | | Q | Joan Billy Bob | 7.00 | +---++--+ 15 rows in set (0.00 sec) -Original Message- From: paul wilczynski [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 07, 2002 1:26 PM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] Can't get left join to work Here are the 2 tables and selected columns from each. Note there are only 4 employees who have trips (3 of those employees active, 1 inactive). If I do the following select, I only get 4 rows returned (or 3 if I say 'where active = 'A'). In addition to those, I want 1 row returned for each of the employees with 0 miles if there are no rows in the 'mileage' table for them. To put it another way, I always want the number of rows returned to be equal to the number of employees rows whose active_sw = 'A'. select e.id,lname,sum(m.miles) from employees e left join mileage m on e.id=m.id where month(m.trip_date) = 3 and substring(year(m.trip_date),3,2) = '02' group by e.id; CREATE TABLE IF NOT EXISTS employees ( id varchar(30) NOT NULL, fname varchar(25) NOT NULL, lname varchar(30) NOT NULL, password varchar(12) NOT NULL, accum_miles_for_year real(7,2), active_sw varchar(1) NOT NULL, registration varchar(10), engine_sizesmallint unsigned, fuel_reimburse_ratereal(3,2) default 0, PRIMARY KEY (id)) ID FNAME ACTIVE_SW 2SamA 3Fred RobertA 4Paul A 121212121George A 121212122George C. A 121212123George A ABCABCABCIanA BBobA QJoan Billy Bob A 123123124Jake A 021021021SamA 893458009Joan H A 6902DIanA 123412342Paul A 138501999ShermanI CREATE TABLE IF NOT EXISTS mileage ( recno mediumint not null auto_increment, id varchar(30) NOT NULL, trip_date date NOT NULL, trip_from varchar(30) NOT NULL, trip_to varchar(30), purpose varchar(30), miles real(7,2) unsigned NOT NULL, PRIMARY KEY (recno)) ID TRIP_DATEMILES 138501231 2002-03-01 5200.00 ABCABCABC 2002-03-0180.00 6902D 2002-03-0113.00 6902D 2002-03-0113.00 6902D 2002-03-0180.00 6902D 2002-03-0180.00 6902D 2002-03-0113.00 6902D 2002-03-0113.00 6902D 2002-03-0113.00 6902D 2002-03-0113.00 6902D 2002-03-0113.00 6902D 2002-03-0113.00 6902D 2002-03-0113.00 6902D 2002-03-0114.00 6902D 2002-03-0140.00 6902D 2002-03-0144.00 6902D 2002-03-0144.00 6902D 2002-03-01 800.00 6902D 2002-03-01 5.00 6902D 2002-03-01 148.00 Q 2002-03-01 7.00 --- Rick Emery wrote ... Show us your table structures and the data they contain. It appears your WHERE clause probably acting correctly. But without data, we can determine that. -Original Message- From: paul wilczynski [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 07, 2002 11:49 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] Can't get left join to work I've got 2 MySQL tables: Employees, and Mileage. Mileage records trips for employees and there may be 0 or more Mileage rows for each Employee row. The Mileage table has a trip_date column. For testing purposes, the trip_date column in all rows is set to 2002-03-01. There are currently 15 rows in the Employees table. I want the result of the following select to be 15 rows (1 for each employee, whether or not there are any rows in the Mileage table for that employee). If I take the "where&
Re: [PHP-DB] Can't get left join to work
Here are the 2 tables and selected columns from each. Note there are only 4 employees who have trips (3 of those employees active, 1 inactive). If I do the following select, I only get 4 rows returned (or 3 if I say 'where active = 'A'). In addition to those, I want 1 row returned for each of the employees with 0 miles if there are no rows in the 'mileage' table for them. To put it another way, I always want the number of rows returned to be equal to the number of employees rows whose active_sw = 'A'. select e.id,lname,sum(m.miles) from employees e left join mileage m on e.id=m.id where month(m.trip_date) = 3 and substring(year(m.trip_date),3,2) = '02' group by e.id; CREATE TABLE IF NOT EXISTS employees ( id varchar(30) NOT NULL, fname varchar(25) NOT NULL, lname varchar(30) NOT NULL, password varchar(12) NOT NULL, accum_miles_for_year real(7,2), active_sw varchar(1) NOT NULL, registration varchar(10), engine_sizesmallint unsigned, fuel_reimburse_ratereal(3,2) default 0, PRIMARY KEY (id)) ID FNAME ACTIVE_SW 2SamA 3Fred RobertA 4Paul A 121212121George A 121212122George C. A 121212123George A ABCABCABCIanA BBobA QJoan Billy Bob A 123123124Jake A 021021021SamA 893458009Joan H A 6902DIanA 123412342Paul A 138501999ShermanI CREATE TABLE IF NOT EXISTS mileage ( recno mediumint not null auto_increment, id varchar(30) NOT NULL, trip_date date NOT NULL, trip_from varchar(30) NOT NULL, trip_to varchar(30), purpose varchar(30), miles real(7,2) unsigned NOT NULL, PRIMARY KEY (recno)) ID TRIP_DATEMILES 138501231 2002-03-01 5200.00 ABCABCABC 2002-03-0180.00 6902D 2002-03-0113.00 6902D 2002-03-0113.00 6902D 2002-03-0180.00 6902D 2002-03-0180.00 6902D 2002-03-0113.00 6902D 2002-03-0113.00 6902D 2002-03-0113.00 6902D 2002-03-0113.00 6902D 2002-03-0113.00 6902D 2002-03-0113.00 6902D 2002-03-0113.00 6902D 2002-03-0114.00 6902D 2002-03-0140.00 6902D 2002-03-0144.00 6902D 2002-03-0144.00 6902D 2002-03-01 800.00 6902D 2002-03-01 5.00 6902D 2002-03-01 148.00 Q 2002-03-01 7.00 --- Rick Emery wrote ... Show us your table structures and the data they contain. It appears your WHERE clause probably acting correctly. But without data, we can determine that. -Original Message- From: paul wilczynski [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 07, 2002 11:49 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] Can't get left join to work I've got 2 MySQL tables: Employees, and Mileage. Mileage records trips for employees and there may be 0 or more Mileage rows for each Employee row. The Mileage table has a trip_date column. For testing purposes, the trip_date column in all rows is set to 2002-03-01. There are currently 15 rows in the Employees table. I want the result of the following select to be 15 rows (1 for each employee, whether or not there are any rows in the Mileage table for that employee). If I take the "where" clause out, I get all 15 rows (including a number of rows where sum(m.miles) = 0 (which is what I want). If I leave the "where" clause in, however, I don't get employees with sum(m.miles) = 0 (even though the "where" clause should include all of the Mileage rows). (Only 3 Employees actually have corresponding rows in the Mileage table, and I only get 3 rows back.) select e.id,lname,sum(m.miles) from employees e left join mileage m on e.id=m.id where month(m.trip_date) = 3 and substring(year(m.trip_date),3,2) = '02' group by e.id; I need to select by month/year from the Mileage table. Any idea how the "select" can be modified to include rows for all of the Employees? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Can't get left join to work
Show us your table structures and the data they contain. It appears your WHERE clause probably acting correctly. But without data, we can determine that. -Original Message- From: paul wilczynski [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 07, 2002 11:49 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] Can't get left join to work I've got 2 MySQL tables: Employees, and Mileage. Mileage records trips for employees and there may be 0 or more Mileage rows for each Employee row. The Mileage table has a trip_date column. For testing purposes, the trip_date column in all rows is set to 2002-03-01. There are currently 15 rows in the Employees table. I want the result of the following select to be 15 rows (1 for each employee, whether or not there are any rows in the Mileage table for that employee). If I take the "where" clause out, I get all 15 rows (including a number of rows where sum(m.miles) = 0 (which is what I want). If I leave the "where" clause in, however, I don't get employees with sum(m.miles) = 0 (even though the "where" clause should include all of the Mileage rows). (Only 3 Employees actually have corresponding rows in the Mileage table, and I only get 3 rows back.) select e.id,lname,sum(m.miles) from employees e left join mileage m on e.id=m.id where month(m.trip_date) = 3 and substring(year(m.trip_date),3,2) = '02' group by e.id; I need to select by month/year from the Mileage table. Any idea how the "select" can be modified to include rows for all of the Employees? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Can't get left join to work
I've got 2 MySQL tables: Employees, and Mileage. Mileage records trips for employees and there may be 0 or more Mileage rows for each Employee row. The Mileage table has a trip_date column. For testing purposes, the trip_date column in all rows is set to 2002-03-01. There are currently 15 rows in the Employees table. I want the result of the following select to be 15 rows (1 for each employee, whether or not there are any rows in the Mileage table for that employee). If I take the "where" clause out, I get all 15 rows (including a number of rows where sum(m.miles) = 0 (which is what I want). If I leave the "where" clause in, however, I don't get employees with sum(m.miles) = 0 (even though the "where" clause should include all of the Mileage rows). (Only 3 Employees actually have corresponding rows in the Mileage table, and I only get 3 rows back.) select e.id,lname,sum(m.miles) from employees e left join mileage m on e.id=m.id where month(m.trip_date) = 3 and substring(year(m.trip_date),3,2) = '02' group by e.id; I need to select by month/year from the Mileage table. Any idea how the "select" can be modified to include rows for all of the Employees? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
