Combine 2 queries

[999Dom999]

I'm simplifying my actual queries, but hope this gets across what I'm trying to do, here goes:

QUERY 1

SELECT SA_MODEL,SALES_QTY FROM SALES

RESULT

MODEL SALES QTY
ABC123 5
ABC124 5

QUERY 2

SELECT OUT_MODEL,OUT_SALES_QTY FROM TRANS WHERE IT_STATUS = 'A' AND IH_CREDIT = ' AND IH_STKSAVE = '

RESULT

MODEL OUT SALES QTY
ABC123 5
ABC124 5
ABC125 5
ABC126 5

I looking to achieve this result:

RESULT

MODEL SALES QTY OUT SALES QTY
ABC123 5 5
ABC124 5 5

SELECT SA_MODEL,SALES_QTY, (SELECT OUT_MODEL,OUT_SALES_QTY FROM TRANS WHERE IT_STATUS = 'A' AND IH_CREDIT = ' AND IH_STKSAVE = ' AND OUT_MODEL = SA_MODEL) AS OUT_SALES_QTY FROM SALES

I know the above query won't work but I need to look up in another table where OUT_MODEL = SA_MODEL for each of the results in the SALES table. I don't think I can join them as the criteria in the second query would effect the results of the first. Any idea of how I can acheive this?

 

[bborissov]

SELECT Sales.SA_MODEL,
       Sales.SALES_QTY,
       TRANS.OUT_SALES_QTY 
 FROM SALES
INNER JOIN TRANS 
      ON Sales.SA_MODEL= TRANS.OUT_MODEL AND
         TRANS.IT_STATUS = 'A'           AND
         TRANS.IH_CREDIT = '??????????'  AND
         TRANS.IH_STKSAVE = '????????'

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.