I am having some problems getting multiple joins to work. I have the
following code:
mysql> select customers.customer_id, last, first, company,
count(work_order.work_order_number) as WO
-> from customers left join work_order
-> on (customers.customer_id=work_order.customer_id)
-> where customers.last like "G%"
-> group by customers.customer_id
-> order by company, last;
Which returns:
+-------------+------------+------------+-----------------------+----+
| customer_id | last | first | company | WO |
+-------------+------------+------------+-----------------------+----+
| 83 | Gallagher | Sherry | | 1 |
| 84 | Gersh | Duff | | 0 |
| 85 | Glasebrook | Millie | | 1 |
| 86 | Gordley | Amy | | 1 |
| 88 | Grant | Don & Mimi | | 1 |
| 90 | Graziano | Margaret | | 1 |
| 91 | Gressard | Dave | | 1 |
| 93 | Grey | Hellen | | 1 |
| 95 | Grismer | Dennis | | 1 |
| 96 | Guy | Susan | | 1 |
| 334 | Gagnon | Bonnie | Cayuse Prairie School | 1 |
| 87 | Gotschalk | Jill | Gotschalk's Graphics | 1 |
| 94 | Greytak | Don | Old Library Gallery | 1 |
| 92 | Grewe | Judy | Ponderosa Gallery | 1 |
| 89 | Gray | Darryl | Spirit Talk Press | 0 |
+-------------+------------+------------+-----------------------+----+
15 rows in set (0.36 sec)
And this code:
mysql> select customers.customer_id, last, first, company,
count(computers.computer_id) as CPU
-> from customers left join computers
-> on (customers.customer_id=computers.customer_id)
-> where customers.last like "G%"
-> group by customers.customer_id
-> order by company, last;
Which returns:
+-------------+------------+------------+-----------------------+-----+
| customer_id | last | first | company | CPU |
+-------------+------------+------------+-----------------------+-----+
| 83 | Gallagher | Sherry | | 0 |
| 84 | Gersh | Duff | | 1 |
| 85 | Glasebrook | Millie | | 1 |
| 86 | Gordley | Amy | | 0 |
| 88 | Grant | Don & Mimi | | 0 |
| 90 | Graziano | Margaret | | 0 |
| 91 | Gressard | Dave | | 0 |
| 93 | Grey | Hellen | | 0 |
| 95 | Grismer | Dennis | | 0 |
| 96 | Guy | Susan | | 0 |
| 334 | Gagnon | Bonnie | Cayuse Prairie School | 0 |
| 87 | Gotschalk | Jill | Gotschalk's Graphics | 2 |
| 94 | Greytak | Don | Old Library Gallery | 0 |
| 92 | Grewe | Judy | Ponderosa Gallery | 0 |
| 89 | Gray | Darryl | Spirit Talk Press | 1 |
+-------------+------------+------------+-----------------------+-----+
15 rows in set (0.01 sec)
I am trying to combine these 2 into 1 table, I tried the following:
mysql> select customers.customer_id, last, first, company,
count(work_order.work_order_number) as WO, count(computers.computer_id) as
CPU
-> from customers left join work_order
-> on (customers.customer_id=work_order.customer_id)
-> left join computers
-> on (customers.customer_id=computers.customer_id)
-> where customers.last like "G%"
-> group by customers.customer_id
-> order by company, last;
Which gives me:
+-------------+------------+------------+-----------------------+----+-----+
| customer_id | last | first | company | WO | CPU |
+-------------+------------+------------+-----------------------+----+-----+
| 83 | Gallagher | Sherry | | 1 | 0 |
| 84 | Gersh | Duff | | 0 | 1 |
| 85 | Glasebrook | Millie | | 1 | 1 |
| 86 | Gordley | Amy | | 1 | 0 |
| 88 | Grant | Don & Mimi | | 1 | 0 |
| 90 | Graziano | Margaret | | 1 | 0 |
| 91 | Gressard | Dave | | 1 | 0 |
| 93 | Grey | Hellen | | 1 | 0 |
| 95 | Grismer | Dennis | | 1 | 0 |
| 96 | Guy | Susan | | 1 | 0 |
| 334 | Gagnon | Bonnie | Cayuse Prairie School | 1 | 0 |
| 87 | Gotschalk | Jill | Gotschalk's Graphics | 2 | 2 |
| 94 | Greytak | Don | Old Library Gallery | 1 | 0 |
| 92 | Grewe | Judy | Ponderosa Gallery | 1 | 0 |
| 89 | Gray | Darryl | Spirit Talk Press | 0 | 1 |
+-------------+------------+------------+-----------------------+----+-----+
15 rows in set (0.34 sec)
The code appears to work, but customer Id 87 is showing 2 for both WO and
CPU, it should be 1 for WO and 2 for CPU as evidenced by the queries above.
Any help you could provide would be appreciated.
--
Calvin
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
