RE: Need help with a SELECT statement across 3 tables
Dominique: Thanks for your suggestions/ideas. After playing with it for a little while longer of banging my head into a brick wall, I realized I was using a left join when I needed a right. You have my table structures pretty much down - here's the final SQL statement that I use to return 1 row per update per server that hasn't been applied: SELECT update_track.update_id, server.server_id, server.os, update_track.bugtraq_id FROM update_track LEFT JOIN server_update ON (update_track.update_id = server_update.update_id) RIGHT JOIN server ON (server_update.server_id = server.server_id) WHERE server.os = update_track.os AND server_update.server_id IS NULL; Which returns something easy to work with ( from a test set of 2 entries in the update_track table) +---+---+--++ | update_id | server_id | os | bugtraq_id | +---+---+--++ | 5 | 5 | Windows 2000 | 0 | | 5 | 7 | Windows 2000 | 0 | | 5 | 8 | Windows 2000 | 0 | | 5 |13 | Windows 2000 | 0 | | 5 |16 | Windows 2000 | 0 | | 5 |19 | Windows 2000 | 0 | | 5 |20 | Windows 2000 | 0 | | 5 |27 | Windows 2000 | 0 | | 5 |28 | Windows 2000 | 0 | | 5 |30 | Windows 2000 | 0 | | 5 |31 | Windows 2000 | 0 | | 5 |32 | Windows 2000 | 0 | | 5 |39 | Windows 2000 | 0 | | 5 |40 | Windows 2000 | 0 | | 5 |44 | Windows 2000 | 0 | | 5 |49 | Windows 2000 | 0 | | 5 |51 | Windows 2000 | 0 | | 4 |53 | RedHat 9 | 0 | | 5 |56 | Windows 2000 | 0 | | 5 | 104 | Windows 2000 | 0 | | 5 | 123 | Windows 2000 | 0 | | 4 | 532 | RedHat 9 | 0 | | 5 | 165 | Windows 2000 | 0 | | 4 | 295 | RedHat 9 | 0 | | 5 | 327 | Windows 2000 | 0 | | 5 | 361 | Windows 2000 | 0 | | 5 | 364 | Windows 2000 | 0 | | 5 | 388 | Windows 2000 | 0 | | 5 | 403 | Windows 2000 | 0 | | 5 | 405 | Windows 2000 | 0 | | 5 | 406 | Windows 2000 | 0 | | 5 | 407 | Windows 2000 | 0 | | 5 | 408 | Windows 2000 | 0 | | 5 | 424 | Windows 2000 | 0 | | 5 | 430 | Windows 2000 | 0 | | 5 | 455 | Windows 2000 | 0 | | 5 | 457 | Windows 2000 | 0 | | 4 | 467 | RedHat 9 | 0 | | 4 | 529 | RedHat 9 | 0 | | 4 | 512 | RedHat 9 | 0 | | 5 | 533 | Windows 2000 | 0 | | 5 | 554 | Windows 2000 | 0 | | 4 | 556 | RedHat 9 | 0 | | 4 | 558 | RedHat 9 | 0 | | 4 | 565 | RedHat 9 | 0 | | 4 | 575 | RedHat 9 | 0 | | 4 | 601 | RedHat 9 | 0 | | 5 | 614 | Windows 2000 | 0 | +---+---+--++ I think I should be able to claim SQL as a second language - you can say so much with it! Brandon Ewing -Original Message- From: Dominique Plante [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 27, 2004 4:43 PM To: 'Brandon Ewing' Subject: RE: Need help with a SELECT statement across 3 tables Brandon: I have been toying with your problem, and unfortunately, I have yet to come up with a good solution, since I am interested in seeing what the solution would be. Maybe you can confirm a few things. Do your table structures look anything like this? Server: | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | server_id | int(11) | | PRI | NULL| auto_increment | | location | varchar(30) | YES | | NULL|| | os| varchar(30) | YES | | NULL|| Server_update: | Field | Type | Null | Key | Default | Extra | ++---+--+-+-+---+ | server_id | int(11) | | | 0 | | | update_id | int(11) | | | 0 | | | updateDateTime | timestamp | YES | | NULL| | Update_track: | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | update_id
Need help with a SELECT statement across 3 tables
Isn't it great when you've got this pretty picture in your head about what you want SQL to do for you, but aren't sure how to write it down? I've got 3 tables, they are: server - a table that tracks all of our servers, including os, where they are, access details, etc update_track - a table that tracks updates that need to be applied to servers. server_update - a table that tracks what updates have been applied to what servers. An update_track entry contains a update_id, the primary key, a description, the afflicted OS, and the bugtraq ID associated with the update. So there's multiple entries for each bugtraq id for the OS's we support. The server_update table contains update_id, server_id, and a datestamp for when the update was applied. What I want to do is be able to list all servers that do not have all updates applied, based on OS. So we have to take each update_id, compare the update OS against a server entry OS, and decide if it matches, then check the server_update table to see if the update is applied already. I imagine that it would be sorted by server_id, then update_id. Has anyone done anything similar before, and can help me out? TIA 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
-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
-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
|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]
