Issue with joining the tables correctly

  • I have such a difficulty.

    I want every record every record in WHSIBLM where IBONHD is not 0. But I only want to limit the data in the WHSDMQT table to only bring in records where the mdmovt ='SKR'. I'm afraid my where statement is limiting the WHSIBLM data to only those items that have a record in WHSDMQT.

    Does someone understand what I do wrong?

     

    SELECT w.IBWHS# AS 'DC', w.IBITM# AS 'Item Number', w2.SLZONE As 'Location Zone', w.IBHGHT AS 'Height Dimensions', 
    w.IBWDTH AS 'Width Dimensions', w.IBLGTH AS 'Length Dimnesions', w3.ITDES1 AS 'Title', w3.ITPTYP AS 'Publisher Type', w3.ITRPRC AS 'MSRP',
    CAST(w3.ITCOST AS decimal (18,2)) AS 'WAC', r.RCLDTE AS 'Current last receipt', r2.RCLDTE AS 'Historic last receipt', w4.MDMOVT AS 'Pending kills',
    s.DESDTE AS 'Last Ship Date', SUM(w.IBONHD) AS 'On hand quantity'

    FROM LEVYDTA.WHSIBLM w
    INNER JOIN LEVYDTA.WHSDMQT w4
    ON w.IBITM#=w4.MDITM#
    LEFT OUTER JOIN LEVYDTA.WHSLBLM w2
    ON w.IBWHS#=w2.SLWHS# AND w.IBITM#=w2.SLITM#
    LEFT OUTER JOIN LEVYDTA.WHSITMM w3
    ON w.IBITM#=w3.ITITM#
    LEFT OUTER JOIN LEVYDTA.RECTRNT r
    ON w.IBITM#=r.RCITM#
    LEFT OUTER JOIN LEVYDTA.RECTRNH r2
    ON w.IBITM#=r2.RCITM#
    LEFT OUTER JOIN SMPDTA.SMPDTLM s
    ON w.IBITM#=s.DEITM#

    WHERE w.IBONHD <>0 AND w4.MDMOVT = 'SKR' AND s.DEIO='o' AND s.DESDTE BETWEEN 20210801 AND 20210802

    GROUP BY w.IBWHS#, w.IBITM#, w2.SLZONE, w.IBHGHT, w.IBWDTH, w.IBLGTH, w3.ITDES1, w3.ITPTYP, w3.ITRPRC, w3.ITCOST, r.RCLDTE, r2.RCLDTE, w4.MDMOVT,
    s.DESDTE
  • By putting

     AND w4.MDMOVT = 'SKR'

    into the WHERE clause, you effectively turned the LEFT JOIN into an inner join. Move it to the LEFT JOIN - i.e.,

    LEFT OUTER JOIN LEVYDTA.WHSLBLM w2
    ON w.IBITM#=w4.MDITM# AND w4.MDMOVT = 'SKR'

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply