andrewlocatelliwoodcock

Thoughts on Software

How and why to use LEFT JOINs in SQL statements

with one comment

LEF T JOINs are something I’ve been using in SQL statements for literally years without thinking much about it but a few conversations recently have made me realize that with the rise of ORMs, a lot of people are a lot less SQL-savvy than they were even a few years ago to the point that JOINs are a bit of a mystery. Most people seem to be able to use INNER JOINs correctly but LEFT JOINs cause a lot of confusion and hence this post …

A LEFT JOIN is used to return data from two tables where there are definitely rows in one table and there may be corresponding rows in the other. If there are, we want to see them and if there aren’t we don’t care: that is, we don’t want to see data only where there is data present in both tables. An example of this could be a report showing all students enrolled in college course and their grades, where some students may not yet have taken any exams but we still want to see all students and then any results for exams they have taken.

So, how do we do this?

Continuing with our example, we’ll need three tables, Students, Courses and Grades with the following schema:

Students: Id, FirstName, SecondName
Courses: Id, Description
Grades: StudentId, CourseId, Grade

What we want to see is all students and every grade they have received over the year. We’ll also want to see the course description so we’ll need to JOIN all three tables. Here’s how we do it:

SELECT 
      s.Id, s.FirstName, s.SecondName, g.Grade, c.Description 
FROM
      Students s
LEFT JOIN Grades g ON s.Id = g.StudentId
INNER JOIN Courses c ON c.Id = g.CourseId

(And just to explain s is declared as an alias of Students so s.Id is the same as writing Students.Id, etc. …)

The LEFT JOIN means: “give me everything on the left of the equals sign and any matching rows on the right of the equals sign”. So

FROM
      Students s
LEFT JOIN Grades g ON s.Id = g.StudentId

means: “give me everything from Students and any matching rows from the Grades table”

So, why the INNER JOIN on Courses? This is what allows us to get the course description from the Courses table. We are assuming here that there should never be a grade for a course that doesn’t exist (pretty reasonable assumption!), so we are restricting the grades we are returning to only those with a matching course.

So there you have it: how to return all students and any matching grades complete with course description in one simple SQL statement.

Later in the week, I’ll be publishing a post on how to use LEFT JOINs to find missing data …

Written by andrewlocatelliwoodcock

May 30, 2012 at 22:11

Posted in Databases, mySQL, SQL, SQL Server

Tagged with , ,

Creating a table directly from a SELECT statement in mySQL

with one comment

It is often useful to be able to select some data we are working with into a new table for further analysis. This is often achieved by first creating the new table and then populating it via a separate SQL statement but it can be quite time-consuming, especially when we want to work with larger numbers of columns, to write the full CREATE TABLE statement for the new table.

What would be ideal would be to be able to infer the structure of the new table directly from the SELECT statement and mySQL does actually give us a way to do that: the CREATE TABLE … SELECT statement.

Here’s an example of how it works:

CREATE TABLE my_new_working_table SELECT column_a, column_b, column_d, column_f FROM my_original_table;

And that’s it: mySQL will create the new table for you, inferring the correct structure from the SELECT statement and also insert the data that matches the SELECT. It won’t add indexes or the like but these can be added afterwards if required and when using this approach, often we only need the table for a short-term analysis task anyway.

Written by andrewlocatelliwoodcock

May 21, 2012 at 21:39

Posted in Databases, mySQL, SQL

Tagged with , ,

In mySQL, how do you find all tables that have foreign key constraints against another table

with one comment

The situation is that I need to know which tables in a database hold a foreign key constraint against a particular table, let’s call it TableX. The reason I need to know this is because I am planning to rename and retire TableX and in mySQL at least, the foreign key contraints follow the table rename. So really, I’m looking for metadata about my database and searching it for references to TableX.

It turns out that this is actually pretty simple:

USE information_schema;
SELECT * FROM KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'TableX' AND TABLE_SCHEMA='[my database name]';

This query will give you all the foreign keys in the named database that reference TableX. To find all foreign keys in the database is even simpler (although I can’t think of a use case for it at the moment):

USE information_schema;
SELECT * FROM KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='bigfishgames';

Simples. Once you know how …

Written by andrewlocatelliwoodcock

May 8, 2012 at 21:53

Posted in Databases, mySQL, SQL

Tagged with , ,

Mac keyboard navigation for Windows users: jump to start or end of line

leave a comment »

Another useful Mac keyboard navigation shortcut that I stumbled across whilst trying to work out how to navigate left and right one word at a time: jumping to start and end of a line.

CMD + right key and CMD + left key

These shortcuts work on Mac Air too

Written by andrewlocatelliwoodcock

May 7, 2012 at 19:22

Posted in Mac

Mac keyboard navigation for Windows Users: CTRL + right equivalent

with one comment

This one has been driving me nuts for a while now as I tend to use to the keyboard a lot to navigate documents: hwo on Earth do you navigate right and left one work at a time on a Mac?

ALT + right key and ALT + left key

Simples once you know …

Written by andrewlocatelliwoodcock

May 6, 2012 at 11:21

Posted in Mac

Getting Broadcom BCM4311 Wireless card working with Ubuntu 11.10

with one comment

There seems to be a wide range of problems getting Broadcom BCM4311 wireless cards working with Ubuntu 11.10. I went through a number of the online solutions without getting the wireless card working. So, this post won’t be a complete solution to all issues but did resolve my particular problem:

sudo apt-get update
sudo apt-get remove firmware-b43-installer
sudo apt-get install firmware-b43-installer
sudo reboot

The first command runs update, the second removes the b43 firmware installer that is already on my machine but presumably not working, the next command installs the latest version it can find and then I reboot the machine. I needed to remove the firmware-b43-installer as when I ran

sudo apt-get install firmware-b43-installer

it kept reporting that the latest version was already installed. You may also need to run:

sudo apt-get install b43-fwcutter

Written by andrewlocatelliwoodcock

January 29, 2012 at 13:22

Posted in Ubuntu

How to distinguish HTTP POST action methods at runtime: Checking HTTPContext Server Variables

leave a comment »

Following on from my last post, there is an alternative method for distinguishing whether we are dealing with an HTTP POST. Before, I suggested checking the ActionDecriptor to see whether there was an HttpPost attribute applied. An alternative to this is to check the ControllerContext for the REQUEST_METHOD server variable. If this is “POST”, then this is an HTTP POST.

Example:

public static bool IsHttpPost(ControllerContext context) 
{ 
    return context.RequestContext.HttpContext.Request.ServerVariables.Get("REQUEST_METHOD").ToUpper() == "POST"; 
}

 

Written by andrewlocatelliwoodcock

January 26, 2012 at 18:03

How to distinguish HTTP POST action methods at runtime

with one comment

I had a requirement recently to only apply an action filter to HTTP POST action methods on the controller. So the task was to create a conditional filter provider (which I will cover in a later post) which would only return the attribute if the action also had the HttpPostAttribute applied.

It turns out that it is actually quite simple to discover whether the HttpPostAttribute has been applied if you have access to an ActionDescriptor object, which you can get at from the FilterContext object available to filter attributes in their OnActionExecuting, OnActionExecuted, OnResultExecuting and OnResultExecuted methods as well as in the filter provider. Then it’s just a case of using the GetCustomAttributes and a bit of LINQ to find out if any of the action’s attributes are an HttpPostAttribute:

 

public static bool IsHttpPost(ActionDescriptor action)
{
    return action.GetCustomAttributes(typeof(HttpPostAttribute), true).Any();
}

Simples!

Written by andrewlocatelliwoodcock

January 23, 2012 at 21:11

Debugging PHP: Setting up XDebug with XAMPP on Mac OSX

with 9 comments

Have been introduced to PHP recently when I was asked to take over a site developed for some friends by a third party. It is basically a customized WordPress site and the original developer had done a good job on it. I installed PHPStorm (I’m a big JetBrains fan), XAMPP (an easy to install Apache, PHP, mySQL setup) and then spent a number of very frustrating hours trying to get things working. Pretty permalinks was one issue I encountered (see my earlier post) and once that was resolved I needed to get debugging working. Now, I admit that I am very much a PHP noob but surely it shouldn’t be this fiddly to simply debug a website? ASP.Net / MVC just work out of the box for example …

Read the rest of this entry »

Written by andrewlocatelliwoodcock

January 3, 2012 at 19:58

Posted in Apache, PHP, XAMPP, XDebug

WordPress permalinks not working: some tips on resolving

with 13 comments

Well I was bashing my head off this one for a while! In the hopes of saving others some heartache, I’m going to put my problem and the resolution up here …

The basic issue was that I had inherited a very nice WordPress-based site and needed to set it up locally in order to debug some issues. I brought the website locally to my dev box via FTP, setup XAMPP, brought down and restored the database (tip: remember to change the siteurl and home values in wp_options to the local URL of your website … ) and modified the Apache’s httpd.conf file to recognize the file path where I had setup my local website. So far, so good. I ran the website and saw the lovely home page but as soon as I clicked any link on the page, I was booted back to the local XAMPP homepage.

Read the rest of this entry »

Written by andrewlocatelliwoodcock

December 22, 2011 at 18:42

Posted in Apache, Permalink, Wordpress

Tagged with , ,