'Unexpected results from this SQL - not getting as many records as expected

I have the following SQL:

SELECT F3003.IRAN8         AS CUST_NO,
       F3003.IRKIT         AS SHORT_ITEM,
       F3003.IRMCU         AS WC,
       F3003.IRDSC1        AS ROUTE_DESC,
       F0101.ABALPH        AS CUST_NAME,
       F0101.ABAC01        AS DIV,
       F4101.IMLITM        AS ITEM_NO,
       F4101.IMDSC1        AS ITEM_DESC,
       F4101.IMUOM1        AS UOM,
       F41021.LIPQOH / 100 AS ON_HAND
FROM   PROD2DTA.F3003 AS F3003
       INNER JOIN PROD2DTA.F0101 AS F0101
         ON F3003.IRAN8 = F0101.ABAN8
       INNER JOIN PROD2DTA.F4101 AS F4101
         ON F3003.IRKIT = F4101.IMITM
       INNER JOIN PROD2DTA.F41021 AS F41021
         ON F3003.IRKIT = F41021.LIITM
WHERE  F3003.IRAN8 > 0
       AND F3003.IRMCU LIKE '%NXT'
       AND F0101.ABAC01 = 'CON'
       AND F41021.LIPBIN = 'S'

It seems to be getting the information I need, with one strange exception that I don't understand. Below are samples of data from some of these tables.

We will focus on an item with the short item number = 99898.

F3003

IRKIT   IRDSC1           IRMCU  IREFFF      IREFFT      IRAN8
99898   GLOBAL           NXT    07/27/2021  12/31/2040  11676
99898   GLOBAL           NXT    07/27/2021  01/04/2022  11676
99898   GLOBAL           NXT    01/05/2022  12/31/2040  11676

F0101

ABAN8   ABALPH           ABAC01
11676   Company XYZ      CON

F4101

IMITM   IMLITM           IMDSC1                         IMUOM1
99898   7GWPD310150      WIDE WALE D31 COIR SY          SY

F41021

LIITM   LIPBIN   LIPQOH
99898   S        375
99898   S        375
99898   S        375
99898   S        417
99898   S        375
99898   S        229
99898   S        208
99898   S        375

When I run the query above, I get the following results:

CUST NO CUST NAME   SHORT ITEM  ITEM NO     ITEM DESC               UOM ROUTE DESC  WC  ON HAND
11676   Company XYZ 99898       7GWPD310150 WIDE WALE D31 COIR SY   SY  GLOBAL      NXT 208.00
11676   Company XYZ 99898       7GWPD310150 WIDE WALE D31 COIR SY   SY  GLOBAL      NXT 229.00
11676   Company XYZ 99898       7GWPD310150 WIDE WALE D31 COIR SY   SY  GLOBAL      NXT 375.00
11676   Company XYZ 99898       7GWPD310150 WIDE WALE D31 COIR SY   SY  GLOBAL      NXT 417.00

I was expecting to see 8 lines of results because of the 8 lines of data from F41021. I assume there's a subtle bit of SQL logic that I'm missing to get the results I need.

sql


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions