Databases
StackOverflow2013
Postsdbo
POMySQL LEFT JOIN SELECT not selecting all the left side records?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL LEFT JOIN SELECT not selecting all the left side records?
    primarykey
    data
    text
    <p>I'm getting odd results from a <code>MySQL SELECT</code> query involving a <code>LEFT JOIN</code>, and I can't understand whether my understanding of <code>LEFT JOIN</code> is wrong or whether I'm seeing a genuinely odd behavior.</p> <p>I have a two tables with a many-to-one relationship: For every record in <code>table 1</code> there are 0 or more records in <code>table 2</code>. I want to select all the records in table 1 with a column that counts the number of related records in table 2. As I understand it, <code>LEFT JOIN</code> should always return all records on the <code>LEFT</code> side of the statement.</p> <p>Here's a test database that exhibits the problem:</p> <pre><code>CREATE DATABASE Test; USE Test; CREATE TABLE Dates ( dateID INT UNSIGNED NOT NULL AUTO_INCREMENT, date DATE NOT NULL, UNIQUE KEY (dateID) ) TYPE=MyISAM; CREATE TABLE Slots ( slotID INT UNSIGNED NOT NULL AUTO_INCREMENT, dateID INT UNSIGNED NOT NULL, UNIQUE KEY (slotID) ) TYPE=MyISAM; INSERT INTO Dates (date) VALUES ('2008-10-12'),('2008-10-13'),('2008-10-14'); INSERT INTO Slots (dateID) VALUES (3); </code></pre> <p>The Dates table has three records, and the Slots 1 - and that record points to the third record in Dates.</p> <p>If I do the following query..</p> <pre><code>SELECT d.date, count(s.slotID) FROM Dates AS d LEFT JOIN Slots AS s ON s.dateID=d.dateID GROUP BY s.dateID; </code></pre> <p>..I expect to see a table with 3 rows in - two with a count of 0, and one with a count of 1. But what I actually see is this:</p> <pre><code>+------------+-----------------+ | date | count(s.slotID) | +------------+-----------------+ | 2008-10-12 | 0 | | 2008-10-14 | 1 | +------------+-----------------+ </code></pre> <p>The first record with a zero count appears, but the later record with a zero count is ignored. </p> <p>Am I doing something wrong, or do I just not understand what LEFT JOIN is supposed to do?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USKeith Lawrence
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL LEFT JOIN SELECT not selecting all the left side records?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload