RE: Issues with count(), aliases, and LEFT JOINS
-Original Message- From: Brandon Ewing [mailto:[EMAIL PROTECTED] Sent: Monday, December 15, 2003 2:28 AM To: [EMAIL PROTECTED] Subject: Issues with count(), aliases, and LEFT JOINS 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. 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] No insight on any of this? Is it a problem with my SQL statement/schema, or have I run into what might be a bug? Should I start testing with this dataset on other servers? Brandon Ewing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Issues with count(), aliases, and LEFT JOINS
Try changing it to this: ... - COUNT(DISTINCT switch_connect1.switch_id) AS left_port_count, - COUNT(DISTINCT switch_connect2.switch2_id) AS right_port_count, ... I would suggest, if that suggestion fails, that you experiment with just switch_connect.switch_port and switch_connect.switch2_port . Problems are much easier to define and solve if you simplify them down to the basics. If you're able to do what you want with those values Then you can add the complexity of JOINing to other tables and you'll know when it 'should work' and not. Chris -Original Message- From: Brandon Ewing [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 17, 2003 1:59 AM To: [EMAIL PROTECTED] Subject: RE: Issues with count(), aliases, and LEFT JOINS -Original Message- From: Brandon Ewing [mailto:[EMAIL PROTECTED] Sent: Monday, December 15, 2003 2:28 AM To: [EMAIL PROTECTED] Subject: Issues with count(), aliases, and LEFT JOINS 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. 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] No insight on any of this? Is it a problem with my SQL statement/schema, or have I run into what might be a bug? Should I start testing with this dataset on other servers? Brandon Ewing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Issues with count(), aliases, and LEFT JOINS
-Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 17, 2003 8:12 AM To: [EMAIL PROTECTED] Subject: RE: Issues with count(), aliases, and LEFT JOINS Try changing it to this: ... - COUNT(DISTINCT switch_connect1.switch_id) AS left_port_count, - COUNT(DISTINCT switch_connect2.switch2_id) AS right_port_count, ... I would suggest, if that suggestion fails, that you experiment with just switch_connect.switch_port and switch_connect.switch2_port . Problems are much easier to define and solve if you simplify them down to the basics. If you're able to do what you want with those values Then you can add the complexity of JOINing to other tables and you'll know when it 'should work' and not. Chris That fixed it, thanks bunches. There any explaination as to why that made it work? Brandon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Issues with count(), aliases, and LEFT JOINS
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 |