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]