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_size smallint unsigned,
fuel_reimburse_rate real(3,2) default 0,
PRIMARY KEY (id))
ID FNAME ACTIVE_SW
222222222 Sam A
333333333 Fred Robert A
444444444 Paul A
121212121 George A
121212122 George C. A
121212123 George A
ABCABCABC Ian A
BBBBBBBBB Bob A
QQQQQQQQQ Joan Billy Bob A
123123124 Jake A
021021021 Sam A
893458009 Joan H A
99996902D Ian A
123412342 Paul A
138501999 Sherman I
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_DATE MILES
138501231 2002-03-01 5200.00
ABCABCABC 2002-03-01 80.00
99996902D 2002-03-01 13.00
99996902D 2002-03-01 13.00
99996902D 2002-03-01 80.00
99996902D 2002-03-01 80.00
99996902D 2002-03-01 13.00
99996902D 2002-03-01 13.00
99996902D 2002-03-01 13.00
99996902D 2002-03-01 13.00
99996902D 2002-03-01 13.00
99996902D 2002-03-01 13.00
99996902D 2002-03-01 13.00
99996902D 2002-03-01 14.00
99996902D 2002-03-01 40.00
99996902D 2002-03-01 44.00
99996902D 2002-03-01 44.00
99996902D 2002-03-01 800.00
99996902D 2002-03-01 5.00
99996902D 2002-03-01 148.00
QQQQQQQQQ 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
