MySQL example: OUTER JOINs

I often want to know which items in a table are not in another table. In this example, I have two tables of words. The big table has ~68,000 words and the small one has ~29,000. Since they both have the field name, I use an alias in the SELECT statement so that I don’t have to decipher the results. I also put the fields in the same order as the join so that the logic of the query is consistent with the SELECT statement, but you can put them in any order.

Remember that a LEFT OUTER JOIN contains all of the records from the first table. If there is no corresponding record in the second table, the values for its fields are null. 


SELECT SmallTable.word AS SmallWord, BigTable.word AS BigWord
FROM SmallTable
LEFT OUTER JOIN BigTable 
ON  SmallTable.word = BigTable.word
WHERE BigTable.word IS NULL
ORDER BY `SmallTable`.`word` ASC

The query yields 364 words and looks like this:

LEFT OUTER JOIN Result

Just for fun, the reverse query yields 38,776 words and looks like this.


SELECT SmallTable.word AS SmallWord, BigTable.word AS BigWord
FROM BigTable
LEFT OUTER JOIN SmallTable 
ON SmallTable.word = BigTable.word 
WHERE SmallTable.word IS NULL
ORDER BY `BigTable`.`word` ASC

LEFT OUTER JOIN Result 2

Google Calendar Data Not Displaying

I use Google Calendar to display the availability status of an airplane that I share with four others. About a month ago, the web page with the embedded calendar stopped displaying events. I also noticed that events don’t show up on other calendars e.g. the local high school’s vacation schedule.

It took a while to figure out what they broke, but it looks like you can’t view events on embedded calendars unless you are logged in with a Google account. Since I don’t use Google on Safari, they won’t let me view my events. When I open the page on Chrome, where I am logged in, I can view calendars. So now you know.

Practice

Apparently this book Art & Fear: Observations On the Perils (and Rewards) of Artmaking is the source of one of my favorite parables:

The ceramics teacher announced on opening day that he was dividing the class into two groups. All those on the left side of the studio, he said, would be graded solely on the quantity of work they produced, all those on the right solely on its quality.

His procedure was simple: on the final day of class he would bring in his bathroom scales and weigh the work of the “quantity” group: fifty pound of pots rated an “A”, forty pounds a “B”, and so on. Those being graded on “quality”, however, needed to produce only one pot – albeit a perfect one – to get an “A”.

Well, came grading time and a curious fact emerged: the works of highest quality were all produced by the group being graded for quantity. It seems that while the “quantity” group was busily churning out piles of work – and learning from their mistakes – the “quality” group had sat theorizing about perfection, and in the end had little more to show for their efforts than grandiose theories and a pile of dead clay.

Setting colors with an NSString

I wanted to color some letters in an NSAttributed string using a random list of colors that I generated. What I wanted to do was something like this:


NSString colorName = [NSString stringWithFormat:@"%@Color", colorList[i] ];
imageColor = [UIColor colorName];

But you can’t feed an NSString to the UIColor method. Use a selector to do it.
Like this:
 
NSString *colorname = [NSString stringWithFormat:@"%@Color", colorList[i] ];
SEL labelColor = NSSelectorFromString(colorname);
UIColor *imageColor = [Utilities uiColorFromColorName:colorname];
imageColor = [UIColor performSelector:labelColor];

I had to create pink separately since it is not in the Apple supplied color list. However, I don’t really like the other colors so I created a Utility method to create a UIColor from our RGB color values. It generates a UIColor that I can use for my attributed string.


UIColor *imageColor = [Utilities uiColorFromColorName:[lettersExploded[2] lowercaseString] ];



+ (UIColor *)uiColorFromColorName:(NSString *)colorName {

UIColor *imageColor = nil;

    if ( [colorName isEqualToString:@"black"] ) {
        imageColor = [UIColor colorWithRed:0.0f/255.0f green:0.0f/255.0f blue:0.0f/255.0f alpha:1];
    } else if ( [colorName isEqualToString:@"blue"] ) {
        imageColor = [UIColor colorWithRed:8.0f/255.0f green:41.0f/255.0f blue:247.0f/255.0f alpha:1];
    } else if ( [colorName isEqualToString:@"brown"] ) {
        imageColor = [UIColor colorWithRed:95.0f/255.0f green:47.0f/255.0f blue:0.0f/255.0f alpha:1];
    } else if ( [colorName isEqualToString:@"gray"] ) {
        imageColor = [UIColor colorWithRed:140.0f/255.0f green:140.0f/255.0f blue:140.0f/255.0f alpha:1];
    } else if ( [colorName isEqualToString:@"green"] ) {
        imageColor = [UIColor colorWithRed:51.0f/255.0f green:153.0f/255.0f blue:51.0f/255.0f alpha:1];
    } else if ( [colorName isEqualToString:@"orange"] ) {
        imageColor = [UIColor colorWithRed:255.0f/255.0f green:116.0f/255.0f blue:0.0f/255.0f alpha:1];
    } else if ( [colorName isEqualToString:@"pink"] ) {
        imageColor = [UIColor colorWithRed:255.0f/255.0f green:90.0f/255.0f blue:148.0f/255.0f alpha:1];
    } else if ( [colorName isEqualToString:@"purple"] ) {
        imageColor = [UIColor colorWithRed:140.0f/255.0f green:0.0f/255.0f blue:140.0f/255.0f alpha:1];
    } else if ( [colorName isEqualToString:@"red"] ) {
        imageColor = [UIColor colorWithRed:233.0f/255.0f green:17.0f/255.0f blue:0.0f/255.0f alpha:1];
    } else if ( [colorName isEqualToString:@"white"] ) {
        imageColor = [UIColor colorWithRed:255.0f/255.0f green:255.0f/255.0f blue:255.0f/255.0f alpha:1];
    } else if ( [colorName isEqualToString:@"yellow"] ) {
        imageColor = [UIColor colorWithRed:255.0f/255.0f green:255.0f/255.0f blue:0.0f/255.0f alpha:1];
    }

  return imageColor;
}