Showing data from another table if it exists

Question

I am having a hard time trying to get the correct data out of my DB.

I have a couple of tables:

events_template          laser_events
| id | something |       | id  | extid |   added   |
==================       ===========================
|  1 | something |       |  1  |   7   |   added   |
|  2 | something |       |  2  |   4   |   added   |
|  3 | something |       |  3  |   2   |   added   |
|  4 | something |       |  4  |   1   |   added   |
|  5 | something |       |  5  |   9   |   added   |
|  6 | something |       |  6  |   3   |   added   |
|  7 | something |
|  8 | something |
|  9 | something |
| 10 | something |
| 11 | something |
| 12 | something |
| 13 | something |
| 14 | something |

What I am trying to do is get some output that will show me the results of both tables together linked by id and extid, but still show the results from events_template even if there isn't a matching laser_events row.

I've tried something like

SELECT 
     id,  
     extid 
FROM 
     events_template, 
     laser_events 
WHERE 
     events_template.id = laser_events.ext_id;

But that doesn't show me the events_template rows if there isn't a matching laser_events row.

Any help would be appreciated!

Please don't use the syntax table1, table2 WHERE blahblahblah. That syntax was replaced by the ANSI-92 syntax for INNER JOIN and (for your needs) LEFT JOIN, etc. The older syntax is more than 20 years outdated, harder to read, more prone to mistakes, and on some systems doesn't actually work as intended in all cases (and so is deprecated on those systems). — MatBailie
– MatBailie, Commented May 26, 2016 at 12:27

Giorgos Betsos · Accepted Answer · 2016-05-26 12:14:31Z

2

You have to use LEFT JOIN:

SELECT e.id, l.ext_id 
FROM events_template e
LEFT JOIN laser_events  l ON e.id = l.ext_id;

answered May 26, 2016 at 12:14

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

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

3 Comments

Steve Over a year ago

That's great man. one other thing to it that I thought would be trivial but isn't working as I am expecting. I also want to only show results where a timestamp in events_template is greater than 2016-05-25 07:30:00, if I add "WHERE e.timestamp > '2016-05-25 07:30:00' to the end it again only shows the matching rows as before.

Giorgos Betsos Over a year ago

@Steve This should return all records from events_template table for which timestamp > '2016-05-25 07:30:00' irrespective of the LEFT JOIN.

Steve Over a year ago

You're right, I was doing it on l.timestamp by mistake (the tables I made aren't real so got the names mixed up :P) Thanks!

Collectives™ on Stack Overflow

Showing data from another table if it exists

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related