CODEWITHSUNDEEP
mysql
antonita Miras
antonita Miras

Reputation: 3

How to display results from two tables sharing ids?

I have built a MYSQL database with multiple tables (accounts, attachments, categories, email, mailboxes, original, settings, tag). The only two tables in concern is email and attachments. If an email has an attachment, the email and the attachment share an id. Therefore, I want to display all results from the two tables ordered by date.

However, with the code below, only the attachments table was displayed (about 1500 instances) whereas, I want to show the 1500 instances from attachments and all 60K instances from email table 

SELECT
email.from_fld,
email.subj_fld,
email.date_fld,
email.mailto,
email.body_fld,
email.numAttach,
email.attachNames,
email.attachText,
attachments.filename_fld,
attachments.encode_fld,
attachments.attach_fld,
email.id,
attachments.id AS id_0
FROM
email
JOIN attachments
ON email.id = attachments.id
ORDER BY
email.date_fld ASC

I expect all contents of both tables displayed ordered by date

attachments table description 

id  int(11) NO  MUL     
type_fld    varchar(127)    NO          
filename_fld    varchar(127)    NO          
encode_fld  int(11) YES     0   
attach_fld  longblob    NO

email table description 

id  int(11) NO  PRI     auto_increment
from_fld    varchar(255)    NO  MUL     
to_fld  mediumtext  NO          
subj_fld    mediumtext  NO          
date_fld    datetime    NO          
mailbox varchar(255)    NO          
mailto  varchar(127)    NO          
body_fld    longtext    NO          
numAttach   int(11) NO      0   
attachNames mediumtext  NO          
attachText  longtext    NO          
headings    mediumtext  NO

Upvotes: 0

Views: 23

Answers (1)

forpas
forpas

Reputation: 164089

You need a LEFT join from email to attachments because there are emails without attachments.
The INNER join you use shows only the emails which have attachments.
So replace the line:

JOIN attachments

with

LEFT JOIN attachments

Upvotes: 1

Related Questions