Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My problem:

I'm trying to compare to subsets of data within one table, and I've got two methods that partially work and one certainty that there's got to be a more correct way to do it.

The idea here is a table which contains datasets about the same systems over time, and I would like to compare them and especially to see when there are introductions or absences. Allow me to demonstrate with a simple test table:

mysql> select * from gocore;
+-----+------------+------+----------+----------+
| uid | server     | tag  | software | revision |
+-----+------------+------+----------+----------+
|   1 | enterprise | old  | apache   | 2.2.25   |
|   2 | enterprise | new  | apache   | 2.4.6    |
|   3 | enterprise | new  | tomcat   | 7.0.42   |
|   4 | enterprise | old  | geronimo | 2.1.7    |
+-----+------------+------+----------+----------+

In this example there are two datasets - the tag "old" dataset, and the tag "new" dataset. Each reflects a data sample taken at some point in time. For server "enterprise", we have one software package that changed over time (apache), one software package that was introduced (tomcat), and one software package that became absent (geronimo).

My goal: A query that will allow me to summarize the state between "old" and "new":

+------------+----------+----------+----------+
| server     | software | revision | revision |
+------------+----------+----------+----------+
| enterprise | apache   | 2.2.25   | 2.4.6    |
| enterprise | geronimo | 2.1.7    | NULL     |
| enterprise | tomcat   | NULL     | 7.0.42   |
+------------+----------+----------+----------+

It is important for my purposes to be able to see the 'NULL' cells above - I need to know when software has been added or removed from the system. TO BE CLEAR, the table above is not the result of a query - it's me using a text editor to fix up what I was getting to describe what I'm looking for. I need your help to figure out the query that would make that table :)

My Kludges:

If I perform a LEFT JOIN and use the WHERE clause to discriminate between "old" and "new" tags, I get results for only those entries that exist under both tags:

mysql> select old.server, old.software, old.revision, new.software, new.revision
    -> from gocore as old left join gocore as new on old.software = new.software
    -> where old.tag = 'old' and new.tag = 'new';
+------------+----------+----------+----------+----------+
| server     | software | revision | software | revision |
+------------+----------+----------+----------+----------+
| enterprise | apache   | 2.2.25   | apache   | 2.4.6    |
+------------+----------+----------+----------+----------+

My next try was to create two views so that I could perform the JOIN without throwing the tag filter into the mix:

mysql> create view gc_old as select uid,server,tag,software,revision
    -> from gocore where tag = 'old';
Query OK, 0 rows affected (0.00 sec)

mysql> create view gc_new as select uid,server,tag,software,revision
    -> from gocore where tag = 'new';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from gc_old;
+-----+------------+------+----------+----------+
| uid | server     | tag  | software | revision |
+-----+------------+------+----------+----------+
|   1 | enterprise | old  | apache   | 2.2.25   |
|   4 | enterprise | old  | geronimo | 2.1.7    |
+-----+------------+------+----------+----------+
2 rows in set (0.00 sec)

mysql> select * from gc_new;
+-----+------------+------+----------+----------+
| uid | server     | tag  | software | revision |
+-----+------------+------+----------+----------+
|   2 | enterprise | new  | apache   | 2.4.6    |
|   3 | enterprise | new  | tomcat   | 7.0.42   |
+-----+------------+------+----------+----------+
2 rows in set (0.00 sec)

mysql> select old.server, old.software, old.revision, new.revision
    -> from gc_old as old left join gc_new as new
    -> on old.software = new.software;
+------------+----------+----------+----------+
| server     | software | revision | revision |
+------------+----------+----------+----------+
| enterprise | apache   | 2.2.25   | 2.4.6    |
| enterprise | geronimo | 2.1.7    | NULL     |
+------------+----------+----------+----------+
2 rows in set (0.00 sec)

That works better - I now see absence, not just change, but I still don't see introduction. And I had to go create views for this, which strikes me as inflexible as the dataset is added to over time.

My questions:

  1. How can I make sure that all the holes show up, as represented by the NULL cells in the "My goal" section above?
  2. Can I do it within a single query, avoiding the creation of views as a crutch?

Any help you can provide is greatly appreciated. Thanks!

share|improve this question

1 Answer 1

up vote 5 down vote accepted

I think you have to hack it a bit with a derived table, AKA an implicit temporary table, AKA a "subquery in the from clause."

We derive a table we'll call `t` containing each distinct (server,software) from gocore, then left join to gocore twice, once on tag = 'old' and once on tag = 'new'.

SELECT t.server, t.software, o.revision AS old_rev, n.revision AS new_rev
  FROM (SELECT DISTINCT server, software FROM gocore) t
  LEFT JOIN gocore o ON o.server = t.server AND o.software = t.software AND o.tag = 'old'
  LEFT JOIN gocore n ON n.server = t.server AND n.software = t.software AND n.tag = 'new';
share|improve this answer
1  
Perfect, that does work (after minor typo fixes, I edited to fix)! Thanks, not just for the solution but for giving me some keywords (derived / implicit temporary table) to go learn about. –  gowenfawr Sep 30 '13 at 20:55
    
An interesting MySQL quirk that you may bump into is that if you try to create a view from this query, MySQL won't let you because of the "subquery in the from clause," which isn't supported in views... but, the workaround is simple enough... if you (1) make a view that returns "SELECT DISTINCT server, software FROM gocore" and then (2) use that view in this query instead of the derived table, then (3) create a view out of the new query that uses the view instead of the derived table... that works fine. –  Michael - sqlbot Sep 30 '13 at 23:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.