Technology Solutions for Everyday Folks
Visualization of SQL Joins

What's That Join Again?

  • Matt Zaske
  • July 15, 2019

In the interest of hopefully saving Future Me some time, I'm writing this little bit so as not to have to stumble to remember a simple premise:

How do I LEFT JOIN the same table more than once in a query?

For as many times as I've had to join a table more than once (effectively as a certain type of generic lookup table), you'd think I'd have this memorized. But I always get caught forgetting to proper alias both joins.

An Example

In an over-generalized example, let's say I've got a lookup table for common names. A query for a name would look like:

SELECT name FROM common WHERE id = 5;

And some real data referring multiple common names in another:

SELECT value, of, real, things, product, dependency FROM data;

The product and dependency fields are ID's for common names.

I always get caught in the situation where I try this query to "bolt on" a second LEFT JOIN:

SELECT value, of, real, things, common.name, dep.name
FROM data 
LEFT JOIN common ON common.id = data.product 
LEFT JOIN common AS dep ON dep.id = data.dependency;

But it never gives me the expected common name for the dependency table, and then I get frustrated and re-Google it all...

Simple Syntax Fix

If I only just aliased both lookup JOINs it'd be just fine:

SELECT value, of, real, things, cmn.name, dep.name 
FROM data
LEFT JOIN common AS cmn ON common.id = data.product 
LEFT JOIN common AS dep ON dep.id = data.dependency;

By simply aliasing both joins, things work just as expected.

So, Future Me, remember the aliasing and all will be fine...

Headline image © C.L. Moffatt 2018

Comments

Post Comments

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.
CAPTCHA
I completely believe you're a human, but because the Internet exists I have to request you demonstrate it before submitting a comment.