-2

I have two queries

The first one:

SELECT 
    VCRNUM_0 As 'Tranx_Number',
    ACTQTY_0 As 'Quanity',
    sto.CREDAT_0 As 'Create_Date',
    sto.PROD_DATE_0,
    sto.PROD_TIME_0,
    sto.CREUSER_0,
    sto.ITMREF_0,
    sto.VCRNUMORI_0 as 'Work_Order_number',
    itm.ITMWEI_0,
    sto.ACTQTY_0 * itm.ITMWEI_0 As Weight,
    itm.ITMDES1_0 as 'SKU_Description',
    a1.TEXTE_0 As 'Work_Center_Description',
    gope.CPLWST_0 as 'Work_Center_Number'
FROM
    /* STOCK JOURNAL */
    ZSTOJOU sto 
JOIN
    /* WORK ORDER OPERATIONS */
    MFGOPE gope 
    ON sto.VCRNUMORI_0 = gope.MFGNUM_0
LEFT JOIN 
    /* TEXTS */
    ATEXTRA a1 
    ON gope.CPLWST_0= a1.IDENT1_0 
    AND a1.CODFIC_0 = 'WORKSTATIO' 
    AND a1.ZONE_0='WSTDESAXX' 
    AND a1.LANGUE_0='ENG'
LEFT JOIN 
    /* LOCAL MENU */
    APLSTD APL 
    ON sto.TRSTYP_0 = APL.LANNUM_0 
    AND APL.LAN_0= 'ENG' 
    AND APL.LANCHP_0 = 704
LEFT JOIN 
    /* PRODUCTS */
    ITMMASTER itm 
    ON sto.ITMREF_0 = itm.ITMREF_0
LEFT JOIN 
    /* TEXT */
    ATEXTRA a2 
    ON itm.TSICOD_6 = a2.IDENT2_0
    AND a1.CODFIC_0 = 'ATABDIV' 
    AND a2.ZONE_0 = 'LNGDES'
    AND a2.LANGUE_0 = 'ENG'
    AND a2.IDENT1_0 = 26
WHERE 
    sto.TRSTYP_0 = 5
    AND sto.VCRTYPORI_0 = 10
    AND sto.VCRTYPREG_0 = 0
    AND gope.CPLWST_0 NOT IN ('22500L','22600L','225C0L','612B0l','611A0L','214G0','81000L','22050')
    AND gope.CPLWST_0 IS NOT NULL

Then I have a second query:

SELECT TOP 5 
    ITMMASTER.ITMREF_0, 
    ITMMASTER.ITMDES1_0,
    ITMMASTER.TCLCOD_0, 
    a3.TEXTE_0
FROM
    /* PRODUCTS */
    ITMMASTER 
JOIN
    /* CATEGORIES */
    ITMCATEG cat 
    ON ITMMASTER.TCLCOD_0= cat.TCLCOD_0
LEFT
    /* CATEGORIES */ 
    JOIN ATEXTRA a3 
    ON ITMMASTER.TCLCOD_0= a3.IDENT1_0 
    AND a3.CODFIC_0 = 'ITMCATEG' 
    AND a3.ZONE_0='TCLAXX' 
    AND a3.LANGUE_0='ENG'
WHERE
    ITMMASTER.ITMREF_0 ='2AL00HR0'

My question is how do I left join the two?

New contributor
Jeannie is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • The ITMMASTER and ATEXTRA tables from your second query are already part of the first query. Do you need to join to the table a second time based on the ITMMASTER.ITMREF_0 ='2AL00HR0' criteria? Your second query has a TOP 5 in it--is that important to the combined query? Can you explain what results you're looking to achieve when you say you want to "left join the two queries"? – AMtwo Jan 13 at 23:46
  • You join them by employing a join operator; not sure what is your problem here. – mustaccio Jan 14 at 2:28
  • Can you describe what are you trying to achieve? Are you sure the second query returns 5 products? Because you're filtering it by a single product code 2AL00HR0. Maybe you're trying to get 5 products of the same category? – McNets 2 days ago
0
select * 
  from (copypaste query A) as A 
  left join (copypaste query B) as B 
    on <whatever the join predicate is>

Your Answer

Jeannie is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.