Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Sounds like you want to do a <code>LEFT JOIN</code> and then check for results in which the value of the joined table is <code>NULL</code>, indicating that no such joined record exists.</p> <p>For example:</p> <pre><code>SELECT * FROM posts LEFT JOIN metadata ON (posts.pid = metadata.pid AND metadata.k = 'optout') WHERE metadata.mdid IS NULL; </code></pre> <p>This will select any row from the table <code>posts</code> for which no corresponding <code>metadata</code> row exists with a value of <code>k = 'optout'</code>.</p> <p>edit: Worth noting that this is a key property of a left join and would not work with a regular join; a left join will always return values from the first table, even if no matching values exist in the joined table(s), allowing you to perform selections based on the absence of those rows.</p> <p>edit 2: Let's clarify what's happening here with respect to the <code>LEFT JOIN</code> versus the <code>JOIN</code> (which I refer to as an <code>INNER JOIN</code> for clarity but is interchangable in MySQL).</p> <p>Suppose you run either of these two queries:</p> <pre><code>SELECT posts.*, metadata.mdid, metadata.k, metadata.v FROM posts INNER JOIN metadata ON posts.pid = metadata.pid; </code></pre> <p>or</p> <pre><code>SELECT posts.*, metadata.mdid, metadata.k, metadata.v FROM posts LEFT JOIN metadata ON posts.pid = metadata.pid; </code></pre> <p>Both queries produce the following result set:</p> <pre><code>+-----+-------+--------------+------+-------+-----------+ | pid | title | content | mdid | k | v | +-----+-------+--------------+------+-------+-----------+ | 1 | Foo | Some content | 1 | date | yesterday | | 1 | Foo | Some content | 2 | thumb | img.jpg | +-----+-------+--------------+------+-------+-----------+ </code></pre> <p>Now, let's suppose we modify the query to add the extra criteria for "optout" that was mentioned. First, the <code>INNER JOIN</code>:</p> <pre><code>SELECT posts.*, metadata.mdid, metadata.k, metadata.v FROM posts INNER JOIN metadata ON (posts.pid = metadata.pid AND metadata.k = "optout"); </code></pre> <p>As expected, this returns no results:</p> <pre><code>Empty set (0.00 sec) </code></pre> <p>Now, changing that to a <code>LEFT JOIN</code>:</p> <pre><code>SELECT posts.*, metadata.mdid, metadata.k, metadata.v FROM posts LEFT JOIN metadata ON (posts.pid = metadata.pid AND metadata.k = "optout"); </code></pre> <p>This DOES produce a result set:</p> <pre><code>+-----+-------+--------------+------+------+------+ | pid | title | content | mdid | k | v | +-----+-------+--------------+------+------+------+ | 1 | Foo | Some content | NULL | NULL | NULL | +-----+-------+--------------+------+------+------+ </code></pre> <p>The difference between an <code>INNER JOIN</code> and a <code>LEFT JOIN</code> is that an <code>INNER JOIN</code> will only return a result if rows from BOTH joined tables match. In a <code>LEFT JOIN</code>, matching rows from the first table will ALWAYS be returned, regardless of whether anything is found to join to. In a lot of cases it doesn't matter which one you use, but it's important to choose the right one so as not to get unexpected results down the line.</p> <p>So in this case, the suggested query of:</p> <pre><code>SELECT posts.*, metadata.mdid, metadata.k, metadata.v LEFT JOIN metadata ON (posts.pid = metadata.pid AND metadata.k = 'optout') WHERE metadata.mdid IS NULL; </code></pre> <p>Will return the same result set as above:</p> <pre><code>+-----+-------+--------------+------+------+------+ | pid | title | content | mdid | k | v | +-----+-------+--------------+------+------+------+ | 1 | Foo | Some content | NULL | NULL | NULL | +-----+-------+--------------+------+------+------+ </code></pre> <p>Hopefully that clears it up! Joins are a great thing to learn about, having a full understanding of when to use which one is a very good thing.</p>
    singulars
    1. This table or related slice is empty.
    1. POremoving rows from a SELECT based on columns in a different table
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USfutureal
    1. USfutureal
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POremoving rows from a SELECT based on columns in a different table
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSo let me see if I get this... for a post with optout, the subquery matches a metadata row, so the metadata.mdid isn't null, so it doesn't get selected. But a post without optout, the subquery doesn't match a row so the right side gets padded with nulls, so the where clause is true.
      singulars
      1. PO
      1. USalxndr
    2. COI've added another section to the answer related to how the join is working, that should clear up any gray areas with it. Hope that helps!
      singulars
      1. PO
      1. USfutureal
 

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