how to query two tables , some items in table 1 do not an association with table2 ?
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
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.
Try using 'full join' instead of inner join
-
05:30
-
04:00
-
00:00
-
10:30
-
9:10
-
07:40
Firebase Cloud Functions: PubSub, "res.on is not a function"
-
04:00
TypeError: Cannot read properties of undefined (reading 'createMessageComponentCollector')
-
9:20
-
7:40
- typescript: tsc is not recognized as an internal or external command, operable program or batch file 111624 visits
- search for elements in a list 111072 visits
- In Chrome 55, prevent showing Download button for HTML 5 video 98836 visits
- RxJS5 - error - TypeError: You provided an invalid object where a stream was expected 96341 visits
- Ionic 2 - how to make ion-button with icon and text on two lines? 92538 visits
- NetBeans IDE - ClassNotFoundException: net.ucanaccess.jdbc.UcanaccessDriver 62054 visits
- Adding methods to es6 child class 22381 visits
