Unable to get correct data over several joins

Question

I have the following tables and sample data:

Table: activities
+------------------+---------------+
| activity_id (PK) | activity_name |
+------------------+---------------+
|               18 | Bicycling     |
|               19 | Running       |
+------------------+---------------+

Table: activity_attributes
+------------------+-------------------+
| activity_id (FK) | attribute_id (FK) |
+------------------+-------------------+
|               18 |                55 |
|               18 |                56 |
|               18 |                57 |
|               19 |                56 |
+------------------+-------------------+

Table: attributes
+-------------------+----------------+
| attribute_id (PK) | attribute_name |
+-------------------+----------------+
|                55 | City           |
|                56 | Duration       |
|                57 | Bike           |
+-------------------+----------------+

Table: attributes_options
+-------------------+---------------+
| attribute_id (FK) | option_id(FK) |
+-------------------+---------------+
|                55 |            56 |
|                55 |            57 |
|                57 |            58 |
|                57 |            59 |
|                57 |            60 |
|                57 |            61 |
+-------------------+---------------+

Table: options
+----------------+---------------+
| option_id (PK) |  option_name  |
+----------------+---------------+
|             56 | Stockholm     |
|             57 | Vasteras      |
|             58 | My own        |
|             59 | Rented bike   |
|             60 | Borrowed bike |
|             61 | My old bike   |
+----------------+---------------+

At the moment my query looks like this:

SELECT * 
FROM activities a 
LEFT JOIN activity_attributes aa ON a.activity_id = aa.activity_id 
LEFT JOIN attributes at ON aa.attribute_id = at.attribute_id 
LEFT JOIN attributes_options ao ON at.attribute_id = ao.attribute_id 
LEFT JOIN options o ON ao.option_id = o.option_id

However, it is returning a null attribute_id for the attributes which do not have any corresponding options (in this case "Duration"). I have tried with different joins, but in the end this query returns the most accurate data, but still not everything I am looking for.

This is the first time I'm working with relational databases and I'm trying to wrap my mind about it, what can I do to return the correct attribute_id of 56 for those examples?

Answer 1

The reason you are seeing attribute_id as null is because it is null inside the attributes_options table. This makes sense, because there is no option for those examples.

To work around this, you should explicitly set the columns you want to select, from the tables where you would expect the entry to exist. So instead of SELECT *, try this:

SELECT a.activity_id, a.activity_name, at.attribute_id, at.attribute_name, o.option_id, o.option_name
FROM activities a
LEFT JOIN activity_attributes aa ON aa.activity_id = a.activity_id
LEFT JOIN attributes at ON at.attribute_id = aa.attribute_id
LEFT JOIN attributes_options ao ON ao.attribute_id = at.attribute_id
LEFT JOIN options o ON o.option_id = ao.option_id;

Here is an SQL Fiddle example that shows my query and yours, so you can see the difference in results side by side.

In other words, don't select columns from your joining tables (activity_attributes and attributes_options) but select values from the stand alone tables (activities, attributes, and options).