Search code examples
mysqlsqlleft-joininner-join

INNER/LEFT JOIN two tables and extend result with row

I have two tables which both have a row by the name "type". It looks like this:

events:

--------------------------------
|  id  |  title     |  type    |
--------------------------------
|  1   |  EventNo1  |  travel  |
|  2   |  EventNo2  |  info    |
|  3   |  EventNo3  |  travel  |
|  4   |  EventNo4  |  sport   |
|  5   |  EventNo5  |  info    |
--------------------------------

types:

--------------------------------
|  id  |  type    |  label     |
--------------------------------
|  1   |  travel  |  xxx #1    |
|  2   |  info    |  xxx #2    |
|  3   |  sport   |  xxx #3    |
--------------------------------

If "events.type" matches "types.type" I want to extend my query result with the label from types, so it looks like this:

combined:

-------------------------------------------
|  id  |  title     |  type    |  label   |
-------------------------------------------
|  1   |  EventNo1  |  travel  |  xxx #1  |
|  2   |  EventNo2  |  info    |  xxx #2  |
|  3   |  EventNo3  |  travel  |  xxx #1  |
|  4   |  EventNo4  |  sport   |  xxx #3  |
|  5   |  EventNo5  |  info    |  xxx #2  |
-------------------------------------------

How do I accomplish this? I assume I have to use INNER/LEFT JOIN, but the following attempt doesn't give me any rows:

SELECT events.id, events.title, events.type, types.id, types.type, types.label
FROM events 
INNER JOIN types
ON events.type = types.type

UPDATE

Wow.. Turned out there wasn't a problem with the query at all. The reason it didn't return any rows was because the "types" table had some illegal characters in the label rows :)

Solution

  • you miss one s on types.id

    SqlFiddle Demo

    SELECT events.id, events.title, events.type, types.id, types.type, types.label
FROM events 
INNER JOIN types
ON events.type = types.type

    OUTPUT

    | id |    title |   type | id |   type |  label |
|----|----------|--------|----|--------|--------|
|  1 | EventNo1 | travel |  1 | travel | xxx #1 |
|  2 | EventNo2 |   info |  2 |   info | xxx #2 |
|  3 | EventNo3 | travel |  1 | travel | xxx #1 |
|  4 | EventNo4 |  sport |  3 |  sport | xxx #3 |
|  5 | EventNo5 |   info |  2 |   info | xxx #2 |