1

I've created a file-uploader for attachments that AJAX uploads them the moment they are added to an email. I have a page where all email attachments are displayed. However should a user decide not to save the email as a draft and also not send it their attachments are still in the database.

I want to still display non-associated attachments (that have no email id association and thus will not JOIN with the messages table) on the attachments page.

Here is my current query...

SELECT em.date, em.headers_from, em.subject, ea.filename 
FROM email_attachments AS ea 
LEFT JOIN email_messages AS em ON em.id=ea.id_email 
WHERE em.id_user='1' 
ORDER BY date DESC
John
  • 1
  • 13
  • 98
  • 177
  • Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) – philipxy Jul 07 '20 at 00:33

2 Answers2

1

You are having a check on the messages table which is em.id_user=1. Due to this you are not getting the desired out put, move the clause to the join condition.

Like this::

SELECT em.date, em.headers_from, em.subject, ea.filename 
FROM email_attachments AS ea 
LEFT JOIN email_messages AS em ON em.id=ea.id_email and em.id_user='1'
ORDER BY date DESC
Sashi Kant
  • 13,277
  • 9
  • 44
  • 71
1

Turns out I needed a LEFT OUTER JOIN.

SELECT * FROM email_attachments AS ea
LEFT OUTER JOIN email_messages AS em
ON ea.id_email = em.id
WHERE ea.id_user='1'

A visual explenation that I came across that helped is available here...

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

John
  • 1
  • 13
  • 98
  • 177