Greetings,
This is driving me crazy.
I'm running MySQL 4.0.15-standard.
I've got a db that tracks switches, servers they connect to, and connections
between switches.
Schema for server:
+----------------------+----------------------------------------------------
------+
| Field | Type | Null | Key | Default | Extra
|
+----------------------+------------------+------+-----+---------+----------
------+
| server_id | int(10) unsigned | | PRI | NULL |
auto_increment |
| customer_id | int(10) unsigned | YES | MUL | NULL |
|
| rack_id | int(10) unsigned | | MUL | 0 |
|
| distance_from_bottom | int(10) unsigned | YES | | NULL |
|
| switch_id | int(10) unsigned | | MUL | 0 |
|
| switch_port | int(10) unsigned | | | 0 |
|
| size | int(10) unsigned | YES | | NULL |
|
| label | varchar(50) | | | |
|
| base_hostname | varchar(50) | | | |
|
| base_ip | varchar(50) | | | |
|
| access_info | text | YES | | NULL |
|
| monitor | tinytext | YES | | NULL |
|
| hardware | text | YES | | NULL |
|
| addsoft | tinytext | YES | | NULL |
|
+----------------------+----------------------------------------------------
------+
Schema for switch:
+----------------------+------------------+------+-----+---------+----------
------+
| Field | Type | Null | Key | Default | Extra
|
+----------------------+------------------+------+-----+---------+----------
------+
| switch_id | int(10) unsigned | | PRI | NULL |
auto_increment |
| rack_id | int(10) unsigned | | MUL | 0 |
|
| name | varchar(10) | YES | | NULL |
|
| ports | int(10) unsigned | YES | | NULL |
|
| mrtg_prefix | varchar(30) | YES | | NULL |
|
| size | int(10) unsigned | YES | | NULL |
|
| distance_from_bottom | int(10) unsigned | YES | | NULL |
|
+----------------------+------------------+------+-----+---------+----------
------+
Schema for switch_connect:
+--------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| switch_id | int(10) unsigned | | MUL | 0 | |
| switch_port | int(10) unsigned | | | 0 | |
| switch2_id | int(10) unsigned | | MUL | 0 | |
| switch2_port | int(10) unsigned | | | 0 | |
+--------------+------------------+------+-----+---------+-------+
The switch_connect table tracks connections between two switches:
mysql> select * from switch_connect;
+-----------+-------------+------------+--------------+
| switch_id | switch_port | switch2_id | switch2_port |
+-----------+-------------+------------+--------------+
| 1 | 29 | 2 | 1 |
| 1 | 28 | 5 | 1 |
| 1 | 30 | 4 | 1 |
| 1 | 32 | 3 | 1 |
+-----------+-------------+------------+--------------+
Now, I'm trying to get a count of used ports on switches. I was getting
some weird results, and have broken it down this far:
mysql> SELECT
-> switch.switch_id,
-> switch.rack_id,
-> switch.ports,
-> COUNT(switch_connect1.switch_id) AS left_port_count,
-> COUNT(switch_connect2.switch2_id) AS right_port_count,
-> COUNT(server.server_id) AS server_port_count
-> FROM switch
-> LEFT JOIN
-> switch_connect AS switch_connect1 ON (switch.switch_id =
switch_connect1.switch_id)
-> LEFT JOIN
-> switch_connect AS switch_connect2 ON (switch.switch_id =
switch_connect2.switch2_id)
-> LEFT JOIN
-> server ON (switch.switch_id = server.switch_id)
-> GROUP BY switch.switch_id;
+-----------+---------+-------+-----------------+------------------+--------
-----------+
| switch_id | rack_id | ports | left_port_count | right_port_count |
server_port_count |
+-----------+---------+-------+-----------------+------------------+--------
-----------+
| 1 | 1 | 48 | 4 | 0 |
0 |
| 2 | 2 | 24 | 0 | 3 |
3 |
| 3 | 49 | 24 | 0 | 17 |
17 |
| 4 | 43 | 24 | 0 | 19 |
19 |
| 5 | 45 | 24 | 0 | 19 |
19 |
| 6 | 3 | 24 | 0 | 0 |
9 |
| 7 | 4 | 24 | 0 | 0 |
9 |
| 8 | 5 | 24 | 0 | 0 |
10 |
<snip>
+-----------+---------+-------+-----------------+------------------+--------
-----------+
Switch_id's 2-5 all have an entry in switch_connect table.
The right port count for switch_id's 2-5 should be 1. No more than one.
Instead, it's the same as server_port_count.
If I remove the server_port_count and leave left join server in, I get the
same problem in the right_port_count column.
When I remove the server_port_count column AND the server table from the
join, I get the correct result for the right_port_count:
mysql> SELECT
-> switch.switch_id,
-> switch.rack_id,
-> switch.ports,
-> COUNT(switch_connect1.switch_id) AS left_port_count,
-> COUNT(switch_connect2.switch2_id) AS right_port_count
-> FROM switch
-> LEFT JOIN
-> switch_connect AS switch_connect1 ON (switch.switch_id =
switch_connect1.switch_id)
-> LEFT JOIN
-> switch_connect AS switch_connect2 ON (switch.switch_id =
switch_connect2.switch2_id)
-> GROUP BY switch.switch_id;
+-----------+---------+-------+-----------------+------------------+
| switch_id | rack_id | ports | left_port_count | right_port_count |
+-----------+---------+-------+-----------------+------------------+
| 1 | 1 | 48 | 4 | 0 |
| 2 | 2 | 24 | 0 | 1 |
| 3 | 49 | 24 | 0 | 1 |
| 4 | 43 | 24 | 0 | 1 |
| 5 | 45 | 24 | 0 | 1 |
| 6 | 3 | 24 | 0 | 0 |
| 7 | 4 | 24 | 0 | 0 |
| 8 | 5 | 24 | 0 | 0 |
<snip>
+-----------+---------+-------+-----------------+------------------+
I've been pounding my head against this for a while now - can anyone offer
any illumination as to what exactly I'm screwing up?
I'd prefer not to do any major schema re-altering, but if I must, I must.
Brandon Ewing
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
