CODEWITHSUNDEEP
sqlsubquery
user2615341
user2615341

Reputation: 89

Sub Query in the from clause in SQL

I have two queries and I believe I need to put query number two in the FROM statement of query number one could you tell me the best way to do this please?

QUERY1

SELECT   PARTNO_AUD, PARTNO_ING, COSTSET_AUD, 
        ITEMVER_AUD, PROCNO_AUD, PROCVER_AUD, PROCSTAGE_AUD, 
        ALLITEM_AUD, COSTERR, 
        dbo.SSI_DAVL_FUNC('COSTERR', COSTERR, 'E') AS DESCRIPTION
  FROM  dbo.MBI030
 WHERE  (PARTNO_ING <> N'') 
   AND (PROCNO_AUD <> N'') 
   AND (COSTERR <> N'00')

QUERY2

SELECT  PARTNO_B02PAR, PARTNO_B02COM, 
        QTYOFF / (100 - PSLOSS) * 100 AS QTY
  FROM  dbo.MBB020

PARTNO_AUD is the same as PARTNO_B02PAR and PARTNO_ING is the same as PARTNO_B02COM

Upvotes: 1

Views: 83

Answers (3)

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

Well it looks like you want a join. try

SELECT  q1.PARTNO_AUD, q1.PARTNO_ING, q1.COSTSET_AUD, 
        q1.ITEMVER_AUD, q1.PROCNO_AUD, q1.PROCVER_AUD, q1.PROCSTAGE_AUD, 
        q1.ALLITEM_AUD, q1.COSTERR, 
        dbo.SSI_DAVL_FUNC('COSTERR', q1.COSTERR, 'E') AS DESCRIPTION, q2.qty
FROM  dbo.MBI030 q1
   INNER JOIN 
   (
       SELECT  PARTNO_B02PAR, PARTNO_B02COM, QTYOFF / (100 - PSLOSS) * 100 AS QTY
       FROM  dbo.MBB020
   ) q2 ON q1.PartNO_AUD = q2.PARTNO_B02PAR AND q1.PARTNO_ING = q2.PARTNO_B02COM
WHERE  (q1.PARTNO_ING <> N'') 
    AND (q1.PROCNO_AUD <> N'') 
    AND (q1.COSTERR <> N'00')

The trick is to put the query in parentheses and give it an alias e.g q2.

Works on joins and froms.

Upvotes: 1

sam
sam

Reputation: 445

I am assuming all you want to do is join both the table to see output together

SELECT 
    PARTNO_AUD, 
    PARTNO_ING, 
    COSTSET_AUD, 
    ITEMVER_AUD, 
    PROCNO_AUD, 
    PROCVER_AUD, 
    PROCSTAGE_AUD,
    ALLITEM_AUD, 
    COSTERR, 
    dbo.SSI_DAVL_FUNC('COSTERR', COSTERR, 'E') AS DESCRIPTION,
    QTY 
FROM dbo.MBI030 A
    INNER JOIN (
        SELECT 
            PARTNO_B02PAR, 
            PARTNO_B02COM, 
            QTYOFF / (100 - PSLOSS) * 100 AS QTY 
        FROM dbo.MBB020) B
    ON A.PARTNO_AUD =B.PARTNO_B02PAR 
         AND A.PARTNO_ING =B.PARTNO_B02COM
WHERE (PARTNO_ING <> N'') 
    AND (PROCNO_AUD <> N'') 
    AND (COSTERR <> N'00')

for more info on joins see This Link about Joins

Upvotes: 3

Derek
Derek

Reputation: 8753

something like:

SELECT   PARTNO_AUD, PARTNO_ING, COSTSET_AUD, 
        ITEMVER_AUD, PROCNO_AUD, PROCVER_AUD, PROCSTAGE_AUD, 
        ALLITEM_AUD, COSTERR, 
        dbo.SSI_DAVL_FUNC('COSTERR', COSTERR, 'E') AS DESCRIPTION
  FROM  dbo.MBI030
  inner join dbo.MBB020 on PARTNO_AUD = PARTNO_B02PAR and PARTNO_ING = PARTNO_B02COM
 WHERE  (PARTNO_ING <> N'') 
   AND (PROCNO_AUD <> N'') 
   AND (COSTERR <> N'00')

but you may want to change the things you select, you can add:

PARTNO_B02PAR, PARTNO_B02COM, 
        QTYOFF / (100 - PSLOSS) * 100 AS QTY

if you want.

Upvotes: 1

Related Questions