Trouble Combining two SELECT Statements

Question

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.

I have the following two SQL SELECT statements I need to combine:

1:

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 < o.jaoqty 
    AND o.japcmp = 0 
    AND o.japo = 0  

2:

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 < o.jaoqty 
    AND o.japcmp = 0 
    AND p.hdjob = h.jhjob 
    AND p.hdpo = o.japo 
    AND p.hdseq = o.jaseq

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.

.
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.

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 < 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 < o.jaoqty AND o.japcmp = 0 AND o.japo = 0 AND o.jaopr != 'PT')
ORDER BY h.jhjob, o.jaseq, p.hdrecd

Answer 1

You could try adjusting your Where clause on the original statement as well.

It may be better to avoid multiple selects against the same table if you don't have too.

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 < o.jaoqty
    AND o.japcmp = 0
    AND (o.japo = 0 
        OR (p.hdjob = h.jhjob AND p.hdpo = o.japo AND p.hdseq = o.jaseq 
                  AND o.japo <> 0))

Answer 2

Join again to the jjops, using a different alias, then from that to hpodt :

SELECT 
    h.jhhold, h.jhjob, h.jhrev, 
    o.jadesc, o.jaoqty, o.jacqty, o.japo, o.javend,
    o2.jadesc, o2.jaoqty, o2.jacqty, o2.japo, o2.javend, 
    p.hdprcd, p.hdrecd, p.hdseq
FROM Jhead AS h  
LEFT JOIN
    jjops AS o ON h.jhjob = o.jajob  
LEFT JOIN
    jjops AS o2 ON h.jhjob = o2.jajob   
LEFT JOIN
    hpodt AS p ON h.jhjob = p.hdjob
    AND p.hdpo = o2.japo
    AND p.hdseq = o2.jaseq
    AND p.hdjob = h.jhjob
WHERE 
    h.jhpcmp = 0 
    AND o.jatype = 2 
    AND o.jacqty < o.jaoqty 
    AND o.japcmp = 0 
    AND o.japo = 0;

Note also that I've moved some of the conditions up into the ON clause. You can always move them back to the where clause if you want:

...
LEFT JOIN
    hpodt AS p ON h.jhjob = p.hdjob
WHERE 
    h.jhpcmp = 0 
    AND o.jatype = 2 
    AND o.jacqty < o.jaoqty 
    AND o.japcmp = 0 
    AND o.japo = 0
    AND p.hdpo = o2.japo
    AND p.hdseq = o2.jaseq
    AND p.hdjob = h.jhjob;

however it may produce different results - this last version will not allow the LEFT JOIN to return rows from o2 that don't have rows from p.

Answer 3

Put a UNION between the two statements.

UNION needs to have the same number of columns, so you'll need to do a little extra work.

Add a few nulls to the first select statement to cover the columns that don't exist.

SELECT 
   -- Common to both tables
   h.jhhold, h.jhjob, h.jhrev, 
   o.jadesc, o.jaoqty, o.jacqty, 
   o.japo, o.javend, 
   -- These columns don't exist here.
   -- include them anyway as NULL
   NULL as hdprcd,
   NULL AS hdrecd, 
   NULL AS hdseq  
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 < o.jaoqty 
AND o.japcmp = 0 
AND o.japo = 0
-- The big boy, combining the results of two queries
---
UNION
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 < o.jaoqty 
AND o.japcmp = 0 
AND p.hdjob = h.jhjob 
AND p.hdpo = o.japo 
AND p.hdseq = o.jaseq