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 | +-+--+--+-+-+-
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]
