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 🙂
Advertisement
Answer
you miss one s on types.id
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 |