Databases
StackOverflow2013
Postsdbo
POJOIN works on two tables individually, but not when used together
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POJOIN works on two tables individually, but not when used together
    primarykey
    data
    text
    <p>Edit: nevermind, figured it out, answer below if you're interested.</p> <p>Using Postgresql 8.4.</p> <p>Here's the format of the query I need to run (names and faces have been changed to protect my paranoia. they can be provided if necessary, but this is a direct copy of my query with simple substitution for schema, table and column names):</p> <pre><code>SELECT t1.field, SUM(v3.quantity) as current_qty FROM schema1.child_table t1 JOIN (SELECT DISTINCT t1key FROM schema2.child_transaction t2 WHERE datefield BETWEEN '2012-04-01' AND '2012-04-19') t2 USING (t1key) LEFT JOIN schema1.child_current_status v3 ON t1.t1key = v3.t1key JOIN schema1.parent_table t4 ON t1.t4key = t4.t4key WHERE t4.criteria = 763 GROUP BY t1.field </code></pre> <p>... basically, I need the <code>field</code> from <code>child_table</code> when <code>criteria</code> matches my provided data in <code>parent_table</code>, and when there is at least one relevant transaction in <code>child_transaction</code> during the requested period. If there are quantities in the <code>child_current_status</code> view, we want those as well.</p> <p><code>t1.field</code> can be considered a human readable equivalent to numeric key <code>t4.criteria</code>.</p> <p>My problem is that the query as provided fails to provide quantity data from <code>child_current_status</code> even when there is data there.</p> <p><em>Edit: to clarify, the above query returns <code>field, NULL</code>, where it should return <code>field, current_qty</code></em></p> <p>The problem is related to the <code>t2</code> subquery, because when I change that to a <code>LEFT JOIN</code>, it returns the relevant quantity data:</p> <pre><code>SELECT t1.field, SUM(v3.quantity) as current_qty FROM schema1.child_table t1 LEFT JOIN (SELECT DISTINCT t1key FROM schema2.child_transaction t2 WHERE datefield BETWEEN '2012-04-01' AND '2012-04-19') t2 USING (t1key) LEFT JOIN schema1.child_current_status v3 ON t1.t1key = v3.t1key JOIN schema1.parent_table t4 ON t1.t4key = t4.t4key WHERE t4.criteria = 763 GROUP BY t1.field </code></pre> <p><em>Edit: this modified query returns <code>field, current_qty</code>, but will do so whether there are transactions in <code>child_transaction</code> for the requested time period or not</em></p> <p>... all I've done is turn the first JOIN into a LEFT JOIN, and that query returns with the relevant quantities in <code>child_current_status</code>. If I change the <code>child_current_status</code> to an INNER JOIN, the query returns nothing. The problem with making the first JOIN a LEFT JOIN is that I need it to only return results when there is data returned from the sub query, which there is in this case for the requested time period.</p> <p>To muddy the waters, it works properly for <em>some</em> <code>t4.criteria</code>, 763 is just one that it fails on.</p> <p>What am I missing here?</p> <hr> <p>Short version: From the first query above, when the first JOIN is an INNER JOIN, it causes the LEFT JOIN to return nothing. When it's a LEFT JOIN, the LEFT JOIN on <code>child_current_status</code> returns the proper data, but that negates the purpose of the first JOIN.</p> <p><em>Edit: I've tried moving the LEFT JOIN to the end of the FROM clause, behind the INNER JOINs, with no change</em></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJason
    1. USJason
    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
    1. This table or related slice is empty.
    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