Databases
StackOverflow2013
Postsdbo
POTrouble Combining two SELECT Statements
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTrouble Combining two SELECT Statements
    primarykey
    data
    text
    <p>I am sure there is some easy fix to this. I just have basically no experience with writing SQL statements and so I don't know all of the commands, tricks or syntax to get what I need.</p> <p>I have the following two SQL <code>SELECT</code> statements I need to combine: </p> <p><strong>1:</strong></p> <pre><code>SELECT h.jhhold, h.jhjob, h.jhrev, o.jadesc, o.jaoqty, o.jacqty, o.japo, o.javend FROM Jhead AS h LEFT JOIN jjops AS o ON h.jhjob = o.jajob WHERE h.jhpcmp = 0 AND o.jatype = 2 AND o.jacqty &lt; o.jaoqty AND o.japcmp = 0 AND o.japo = 0 </code></pre> <p><strong>2:</strong> </p> <pre><code>SELECT h.jhhold, h.jhjob, h.jhrev, o.jadesc, o.jaoqty, o.jacqty, o.japo, o.javend, p.hdprcd, p.hdrecd, p.hdseq FROM Jhead AS h LEFT JOIN jjops AS o ON h.jhjob = o.jajob LEFT JOIN hpodt AS p ON h.jhjob = p.hdjob WHERE h.jhpcmp = 0 AND o.jatype = 2 AND o.jacqty &lt; o.jaoqty AND o.japcmp = 0 AND p.hdjob = h.jhjob AND p.hdpo = o.japo AND p.hdseq = o.jaseq </code></pre> <p>Both of these two statements return the results I need. The first statement basically returns results where o.japo = 0 (it is never null). The second returns a result where o.japo is not zero and then I attempt to join another table to the data. I did try stick a UNION ALL in between but all union examples I looked up did not have unqiue WHERE statement per select statement.</p> <p>.<br> I had initially attempted the following statement (below) but was getting multiple lines of data when o.japo = 0 and I could not figure out why it was picking up the extra lines but I suspect it was the second left join joining incorrect/unwanted data. When o.japo = 0 all of the hpodt (p.) fields should have been null but instead were populated with data from somewhere.</p> <pre><code>SELECT h.jhhold, h.jhjob, h.jhrev, o.jadesc, o.jaoqty, o.jacqty, o.japo, o.javend, p.hdprcd, p.hdrecd, p.hdseq FROM Jhead AS h LEFT JOIN jjops AS o ON h.jhjob = o.jajob LEFT JOIN hpodt AS p ON h.jhjob = p.hdjob WHERE (h.jhpcmp = 0 AND o.jatype = 2 AND o.jacqty &lt; o.jaoqty AND o.japcmp = 0 AND p.hdjob = h.jhjob AND o.japo != 0 AND p.hdpo = o.japo AND p.hdseq = o.jaseq AND o.jaopr != 'PT') OR (h.jhpcmp = 0 AND o.jatype = 2 AND o.jacqty &lt; o.jaoqty AND o.japcmp = 0 AND o.japo = 0 AND o.jaopr != 'PT') ORDER BY h.jhjob, o.jaseq, p.hdrecd </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMAK
    1. USMAK
    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. POTrouble Combining two SELECT Statements
      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