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 | int(11) | | PRI | NULL | auto_increment | | description | varchar(100) | | | | | | os | varchar(30) | | | | | | bugTraqID | int(11) | | | 0 | | Here's what I imagined you wanted to do (to make sure I understand your problem correctly): You want to return rows from the server table where all the updates listed in the update_track table haven't been applied. To determine whether an update has been applied, you would have to look in the server_update table. Suppose you had the following data for each table (partial list of columns): Update_track: Update_id=1, os = XP Update_id=2, os = 2k Update_id=3, os = XP Server: Server_id=s1 os=XP; Server_id=s2 os=2k Then if the Server_update table contained: Server_id=s1 update_id=1; Server_id=s1 update_id=3; Then the server with server_id=2 would be a server that doesn't have all updates applied. The server with server_id=1 does, since all the entries with the corresponding update_id for the OS XP from update_track are in the table server_update However, let's say Server_update only had: Server_id=s1 update_id=1; Then both servers, that is, the entry with server_id=1 and server_id=2 would qualify as servers that don't have all updates applied. Now, (assuming everything above is correct), I did come up with a couple of queries that might help: 1. Here is the query that returns all updates that haven't been applied: select server_id, update_track.update_id from update_track left join server_update on server_update.update_id = update_track.update_id where server_update.update_id is null 2. This query will return all the updates applied to a particular server_id: select server.server_id, update_track.update_id, update_track.os from server, update_track, server_update where update_track.update_id = server_update.update_id and server.server_id = server_update.server_id; I did make an observation: If you know the query to list all servers and you know the query to list all servers that have all updates applied, then the set difference of these would give you the same result as having the query that lists all servers that don't have all updates applied. The problem I was having was having was determining how to write the query that would list all servers that have all updates applied. I thought of using functions (MySQL 5.0.0), but it seems you can't use select statements in functions (yet) Hope this helps, and please keep me posted on anything you have come up with. Sincerely, Dominique -----Original Message----- From: Brandon Ewing [mailto:[EMAIL PROTECTED] Sent: Friday, January 23, 2004 5:47 PM To: [EMAIL PROTECTED] Subject: 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]
