Select returning null values in the database

Question

SELECT S.ID_SALES,
       S.ID_CLIENT,
       C.FULL_NAME,
       S.SDATE,
       coalesce(S.billed, 'N') BILLED,
       P.product_description product,
       I.amount,
       I.unit_cost, 
       I.unit_cost * I.amount SUBTOTAL,
       coalesce(I.discount, 0) DISCOUNT,
       (I.amount * I.unit_cost) - coalesce(I.discount, 0) TOTAL

FROM SELES S INNER JOIN CLIENTS C ON C.id_client = S.id_client
             inner join ITEM_SELES I ON I.id_sales = S.id_sales
             INNER JOIN PRODUCTS P on P.id_product = I.id_product
WHERE 1=1
ORDER BY C.FULL_NAME,
         P.product_description

When I give a select in my Database all fields come back as null even having data registered, but when I use only the first INNER JOIN with their fields works normally.

In the form below works normally:

SELECT V.ID_SALES,
       V.ID_CLIENT,
       V.SDATE,
       coalesce(V.billed, 'N') BILLED,
       C.FULL_NAME

FROM SELES V INNER JOIN CLIENTS C ON C.id_client = V.id_client
WHERE 1=1

Answer 1

Try it like this:

SELECT   S.ID_SALES,
         S.ID_CLIENT,
         C.FULL_NAME,
         S.SDATE,
         coalesce(S.billed, 'N') BILLED,
         P.product_description product,
         I.amount,
         I.unit_cost, 
         I.unit_cost * I.amount SUBTOTAL,
         coalesce(I.discount, 0) DISCOUNT,
         (I.amount * I.unit_cost) - coalesce(I.discount, 0) TOTAL
FROM     SELES S,
         CLIENTS C,
         ITEM_SELES I,
         PRODUCTS P
WHERE    C.id_client = S.id_client AND
         I.id_sales = S.id_sales AND
         P.id_product = I.id_product
ORDER BY C.FULL_NAME,
         P.product_description

Answer 2

Leonardo, try as below:

SELECT S.ID_SALES,
       S.ID_CLIENT,
       C.FULL_NAME,
       S.SDATE,
       coalesce(S.billed, 'N') BILLED,
       P.product_description product,
       I.amount,
       I.unit_cost, 
       I.unit_cost * I.amount SUBTOTAL,
       coalesce(I.discount, 0) DISCOUNT,
       (I.amount * I.unit_cost) - coalesce(I.discount, 0) TOTAL

FROM SELES S INNER JOIN CLIENTS C ON C.id_client = S.id_client
             left outer join ITEM_SELES I ON I.id_sales = S.id_sales
             left outer join PRODUCTS P on P.id_product = I.id_product
WHERE 1=1
ORDER BY C.FULL_NAME,
         P.product_description

From what you explained, there are data that satisfy:

C.id_client = S.id_client

However, if no data satisfying:

I.id_sales = S.id_sales

Or

P.id_product = I.id_product

The result of the operation will be an empty set. Even the first Join being satisfied.