Require Select query ?

i want select join query below is data ,

my requirement is that ,i want to display balesize from itemmasterfile and Probale table,

if balesize exit in Probale table then take balesize from Probale table ,if bale size in Probale table is null then it will take from itemmaster file ,

CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),Balesize varchar(50))
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Entrydate DATETIME,Balesize varchar(50))
INSERT INTO #ItemMasterFile VALUES(1,'A','100lbs')
INSERT INTO #ItemMasterFile VALUES(2,'B','100lbs')
INSERT INTO #ItemMasterFile VALUES(3,'C','100lbs')
INSERT INTO #ItemMasterFile VALUES(4,'D','100lbs')
INSERT INTO #ItemMasterFile VALUES(5,'e','100lbs')
INSERT INTO #ItemMasterFile VALUES(6,'f','100lbs')
INSERT INTO #ItemMasterFile VALUES(7,'g','100lbs')
INSERT INTO #ItemMasterFile VALUES(8,'h','100lbs')
INSERT INTO #ItemMasterFile VALUES(9,'K','100lbs')
INSERT INTO #ItemMasterFile VALUES(10,'L','100lbs')
INSERT INTO #ItemMasterFile VALUES(11,'M','100lbs')
INSERT INTO #Probale VALUES(10011,1,1,'01-06-2019',null)
INSERT INTO #Probale VALUES(10012,3,1,'02-06-2019',null)
INSERT INTO #Probale VALUES(10013,11,1,'03-06-2019',null)
INSERT INTO #Probale VALUES(10014,10,1,'08-06-2019',null)
INSERT INTO #Probale VALUES(10015,8,1,'03-06-2019','150 lbs')
INSERT INTO #Probale VALUES(10016,9,1,'08-06-2019','150 lbs')
INSERT INTO #Probale VALUES(10017,9,1,'08-06-2019','150 lbs')

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty6y ago
Accepted answer
Hey try this
SELECT itm.CodeItem,itm.Descriptionitem,prob.BID,ISNULL(prob.prdQTY,0) AS prdQTY,prob.Entrydate,
CASE WHEN prob.Balesize IS NOT NULL THEN prob.Balesize ELSE ITM.Balesize END Balesize
FROM #ItemMasterFile itm LEFT JOIN #Probale prob ON prob.CodeItem = ITM.CodeItem
Ramachandran M6y ago
Hi

Try this,

SELECT *,
CASE WHEN prob.Balesize IS NOT NULL THEN prob.Balesize ELSE ITM.Balesize END Balesize
FROM #ItemMasterFile itm
INNER JOIN #Probale prob ON prob.CodeItem = ITM.CodeItem