SQL OUTER JOIN with empty fields (null) where no result

Question

Hulloes SO!

Okay, so my case is this: I have a db where I need to SELECT from 4 different tables and get results where my foregin keys in one table matches the ids in my other 3 tables.

Structure:

CREATE TABLE IF NOT EXISTS `languages` (
  `id` int(11) NOT NULL,
  `lang_name` varchar(64) COLLATE utf8_bin NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `main_keys` (
  `id` int(11) NOT NULL,
  `project` int(11) NOT NULL,
  `key` text COLLATE utf8_bin NOT NULL,
  `comment` varchar(128) COLLATE utf8_bin NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `projects` (
  `id` int(11) NOT NULL,
  `project_name` varchar(64) COLLATE utf8_bin NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `translations` (
  `id` int(11) NOT NULL,
  `project` int(11) NOT NULL,
  `key` int(11) NOT NULL,
  `language` int(11) NOT NULL,
  `translation` text COLLATE utf8_bin NOT NULL,
  `lastedit` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

The translations table is the table with all the relations (translations.project => project.id, translations.key => main_keys.id, and translations.language => language.id).

Relational overview http://assets.brianemilius.com/locale_relations.png

My current query looks like this

SELECT t.id, mk.key, t.translation, mk.comment, p.project_name, t.lastedit
    FROM translations t
    INNER JOIN languages l ON l.id = t.language
    INNER JOIN main_keys mk ON mk.id = t.key
    INNER JOIN projects p ON p.id = mk.project
    WHERE p.id = '1' AND l.id = '2'

I am selecting from a specific project(p.id) and a specific language(l.id) and I want a result, which shows something like this:

+----+--------------+-------------+---------------------------+-----------------------+---------------------+
| id | key          | translation | comment                   | project_name          | lastedit            |
+----+--------------+-------------+---------------------------+-----------------------+---------------------+
| 2  | Hello World! | Hej verden! | Test key for dev purposes | Locale Administrator  | 2015-02-24 12:37:28 |
+----+--------------+-------------+---------------------------+-----------------------+---------------------+
|    | test key     |             | Test key for dev purposes | Locale Administrator  |                     |
+----+--------------+-------------+---------------------------+-----------------------+---------------------+
|    | test key 2   |             | Test key for dev purposes | Locale Administrator  |                     |
+----+--------------+-------------+---------------------------+-----------------------+---------------------+

Where rows 2 and 3 represent results where there are no translations.

But I only get 1 row result - the top one with id 2 (because there currently only is one translation row in the table translations).

I've tried various combinations of LEFT and RIGHT JOIN in my query, but nothing seems to help. Am I on track or do I need to completely rethink?

jpw · Accepted Answer · 2015-03-08 12:56:13Z

2

I think you need to rethink a bit and use the main_keys table as the main source and left join the other tables:

SELECT t.id, mk.key, t.translation, mk.comment, p.project_name, t.lastedit
FROM main_keys mk
LEFT JOIN translations t ON mk.id = t.key
LEFT JOIN languages l ON l.id = t.language  
LEFT JOIN projects p ON p.id = mk.project 
WHERE p.id = 1 AND l.id = 2 OR l.id IS null;

answered Mar 8, 2015 at 12:56

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL OUTER JOIN with empty fields (null) where no result

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related