how to query two tables , some items in table 1 do not an association with table2 ?

157
January 28, 2018, at 05:43 AM

my database schema is below, each id from email table is associated with an id from attachment table, some do not. for example id 1 from email table have attachment(s) from attachments table (3 entries of id 1 for example), while id 2 from email does not have an id2 in attachments. I tried to query the result using the following, but only attachments field showed. In essence, I want the result to show everything from date x to date y, whether it has an attachment or not.

SELECT CONCAT(email.from_fld , date_fld) AS name, email.body_fld, attachments.attach_fld 
FROM email 
INNER JOIN attachments 
ON email.id = attachments.id
WHERE date_fld >= "2012-01-01 00:00:00" AND date_fld <= "2013-01-01 23:59:59" ORDER BY date_fld ASC;

This is my database Schema

email table
    id      INT
    from_fld    VARCHAR
    to_fld  VARCHAR
    subj_fld    MEDIUMTEXT
    date_fld    DATETIME
    mailbox VARCHAR
    mailto  VARCHAR
    body_fld    LONGTEXT
    numAttach   INT
    attachNames MEDIUMTEXT
    attachText  MEDIUMTEXT
    headings    MEDIUMTEXT
attachments table
    id          INT
    type_fld        VARCHAR
    filename_fld    VARCHAR
    encode_fld  INT
    attach_fld      LONGBLOB
origemail table
    id      INT
    orig_fld    LONGBLOB
tags table
    id          INT
    cat_fld     VARCHAR
    key_fld     VARCHAR
    priority_fld    INT
    notes_fld       MEDIUMTEXT
Answer 1

You want a left join if you want everything in the first table in the from clause:

SELECT CONCAT(e.from_fld , e.date_fld) AS name, e.body_fld, a.attach_fld 
FROM email e LEFT JOIN
     attachments a
     ON e.id = a.id
WHERE e.date_fld >= '2012-01-01' AND
      e.date_fld < '2013-01-02' 
ORDER BY date_fld ASC;

Notice the other changes:

  • I introduced table aliases. These make the query easier to write and read.
  • I qualified all column names, so it is clear where the columns come form.
  • I use the ANSI standard delimiter for string and date constants (a single quote rather than a double quote).
  • I simplified the date comparisons.
Answer 2

Try using 'full join' instead of inner join

Rent Charter Buses Company
READ ALSO
How do I debug HikariCP losing connections?

How do I debug HikariCP losing connections?

I use HikariCP with Play 26

280
How to access a line by its index in mysql cusor?

How to access a line by its index in mysql cusor?

I have a view with the following informations:

195
Django problems with primary keys and data inserts

Django problems with primary keys and data inserts

My console shows this problem when trying to push data into my db

204
How much latency in a page load comes from Wordpress MySQL queries?

How much latency in a page load comes from Wordpress MySQL queries?

It has been noted that Wordpress makes 27 MySQL queries for a page load on a vanilla WordPress install on the default theme and even more as more plugins are added

165