Help Combining 2 Queries

[mtepfer]

Need some assistance on how to proceed with this, have looked at it so long have confused myself by now.

Have a query that returns me payments on hold.
The query looks like this :

SELECT 
       p.scode Property,
       t.scode TenCode, rtrim(t.slastname) + ', ' + rtrim(t.sfirstname) TenName ,
       CASE when sc.sPmtType = 'HAP' then v.ucode else t.scode end PayeeCode,
       CASE when sc.sPmtType = 'HAP' then
          case  rtrim(isnull(pv.sfirstname,'')) when '' then pv.ulastname else rtrim(pv.ulastname) + ', ' + pv.sfirstname end
          when sc.sPMTType = 'PAF' then
              case when isnull(ha.sfirstname,'') = '' then ha.slastname else rtrim(ha.slastname) + ', ' + rtrim(ha.sfirstname) end
          ELSE rtrim(t.slastname) + ', ' + rtrim(t.sfirstname) end PayeeName,
	sc.dtfromdate dtFrom , sc.dttodate dtTo , spmttype PmtType, camt amount
  FROM h8prhsched sc
	join property p on sc.hprop = p.hmy
	join tenant t on sc.htenant = t.hmyperson
	join h8summ s on sc.hsumm = s.hmy
	left join vendor v on v.hmyperson = s.hvendor and sc.sPmtType = 'HAP'
	left join person pv on pv.hmy = v.hmyperson and pv.ipersontype = 5
	left join hmtenant ht on ht.htenant= t.hmyperson
	left join (select hsum , hHABilled11f hHABilled from h8Certif
	      union
	      select hsum , hHABilled12f from h8vouch
	      union
	      select hsum , hHABilled12f from h8Homeownership) pr on pr.hsum = s.hmy
	left join tenant HA on ha.hmyperson = pr.hHABilled WHERE 1=1
	and sc.bPaid = 0
    and sc.cAmt != 0
	and sc.istatus =2
    and sc.dtPayFor between '01/01/00' and (getdate() + 7)

The data returned brings back all payments on hold for a specific payee and the amount along with other fields that you can see in the query, the main focuse is the payee, their unique id number and the amount.

The second Query reduced to just the fields i really need looks like this, where it is calling a view not tables :

Select s.hvendor, s.Hap
FROM vH8_PRHAnalyzeTenantSub2 s
 WHERE 1 = 1
       and s.istatus = 0
       and s.bpaid = 0
       and s.ihap > 0
       and  s.dtfrom between '01/01/00' and (getdate() + 10)

This returns outgoing payments that have yet to be approved but are scheduled to be going out. (HAP is amount)

What is needed to happen is all items from the first query will exist, if the vendor id exists in the second query then display that in the results of the first query.

Example:

Query 1 Results (truncated)

Vendor ID Vendor Name Amount
1 Jones -100
1 Jones -100
2 Smith -50


Query 2 Results

Vendor ID Vendor Name Amount
1 Jones 200
3 Williams 100

So the final Result would look like this :

Vendor ID Vendor Name Amount
1 Jones -100
1 Jones -100
1 Jones 200
2 Smith -50

Am using SQL 2000 on this have tried subqueries but am not getting the desired return so not sure if this is the best way or if i am coding it wrong, any questions or anything i left out let me know.

Thanks in advance for any advice on how to proceed.

 

[djj55]

Would a UNION help?

 

[SQLSister]

Actaully do a Union all unless you want to filter out records whihc might be in both queries. Union all is much faster. Just make sure both queries return the same number of fields in the same order.

"NOTHING is more important in a database than integrity." ESquared