Left join

1

hi,
I'm trying to add to the code below another stock column and in case there is not stock I want to receive 0

my code

SELECT P.PARTNAME AS 'מק"ט', P.PARTDES AS 'תיאור', S.SERIALNAME AS 'פקודה', SP.PARTNAME AS 'עבור מק"ט', system.dbo.tabula_dateconvert(S.PSDATE) AS 'תאריך פקודה', K.ABALANCE/1000 AS 'יתרה לניפוק', system.dbo.tabula_hebconvert(SPL.SUPDES) AS 'ספק מועדף'
FROM PART P, SERIAL S, KITITEMS K, SUPPLIERS SPL, PART SP, PARTPARAM PP
WHERE K.PART = P.PART
AND K.SERIAL = S.SERIAL
AND P.PART = PP.PART
AND PP.SUP = SPL.SUP
AND S.CLOSED <> 'C'
AND K.KITFLAG = 'Y'
AND K.ABALANCE > 0
AND S.PART = SP.PART
AND SPL.SUP <> 1136
AND S.RELEASE = 'Y'
AND SP.PARTNAME NOT LIKE 'BIB-%'

I need to add 2 more tables, WARHSBAL and WAREHOUSES
THE WARHSBAL willl give me the stock
and from WAREHOUSES I will select specific warehouses.
when I add this code, it drops all records that do not have stock. I want to get 0 for all record that do not have stock from WARHSBAL

AND K.PART = WARHSBAL.PART
AND WARHSBAL.WARHS = WAREHOUSES.WARHS
AND WAREHOUSES.WARHSNAME IN('Q-ME', 'Main', 'PARK')

HOW CAN i DO IT?

2

you are using where .. which needs to be changed to left join

example
select from table1 , table 2 where
to
select from table1 a LEFT join table2 b on a.part = b.part where

left join example
https://www.w3schools.com/sql/sql_join_left.asp

3

can you add your code to my code?

thanks

4

hi hope this helps ..

i got till here ...

i want to understand what you mean by this
i want to get 0 for all record that do not have stock from WARHSBAL

which table column is stock ...?? i want to get 0 means ..

SELECT 
        P.PARTNAME AS 'מק"ט'
	  , P.PARTDES AS 'תיאור'
	  , S.SERIALNAME AS 'פקודה'
	  , SP.PARTNAME AS 'עבור מק"ט'
	  , system.dbo.tabula_dateconvert(S.PSDATE) AS 'תאריך פקודה'
	  , K.ABALANCE/1000 AS 'יתרה לניפוק'
	  , system.dbo.tabula_hebconvert(SPL.SUPDES) AS 'ספק מועדף'
FROM 
        PART P JOIN KITITEMS K    ON K.PART   = P.PART
	           JOIN SERIAL S      ON K.SERIAL = S.SERIAL
 	           JOIN PARTPARAM PP  ON P.PART   = PP.PART
	           JOIN SUPPLIERS SPL ON PP.SUP   = SPL.SUP
	           JOIN PART SP       ON S.PART   = SP.PART
		  LEFT JOIN WARHSBAL      ON K.PART = WARHSBAL.PART
               JOIN WAREHOUSES    ON WARHSBAL.WARHS = WAREHOUSES.WARHS
WHERE 
    S.CLOSED <> 'C'
AND 
     K.KITFLAG = 'Y'
AND 
     K.ABALANCE > 0
AND 
     SPL.SUP <> 1136
AND 
     S.RELEASE = 'Y'
AND 
     SP.PARTNAME NOT LIKE 'BIB-%'
5

thanks for the quick respond.

to your question, the column is WARHSBAL.ABALANCE

6

by the way, the code is not correct.

prnt.sc/uac8ch

add https://

7

ok

why is it not correct ???

what does this mean ???
prnt.sc/uac8ch

add https://

8

it's a link to a web site to show you his data as a screenshot.

9

hi

i took a look at your LightShot Screen ..

Please explain to me what is wrong !!! ..

Also if i have sample data . like below . it will help a lot

Example = Create Table

10

in work order number 0062T there are 4 part numbers (left image)
and in your query there are 14 line instead of 4, like the left image.
its cartesian multiplication