Display all records from multiple Tables using Join in SQL Server

Last Reply 5 months ago By pandeyism

Posted 5 months ago
create table #Catagory (CID int, CName varchar(max))
Create table #Sections (SecID int, Secnam varchar(50))
create table #ItemMasterFile(CodeItem int, Descriptionitem varchar(max),SecID int,CID int)
create table #Bigbalprd(BID int, CodeItem int, SecID int, CID int, QTY int)

insert into #Catagory values (1, 'INDIA'),(2, 'INDIA(Cut)'),(3, 'Pakistan'),(4, 'Pakistan(Cut)')

insert into #Sections values (1, 'HR'),(2, 'Baby'),(3, 'Ladies'),(4, 'Mix Rammage')

insert into #ItemMasterFile values (1, 'A', '1', '1'),(2, 'B', '2', '2'),(3, 'C', '3',  '3'),(4, 'D', '4',  null)

insert into #Bigbalprd values (1, '1', '1', '1','1'),(2, '3', '3', '3','1'),(3, '4', '4', null,'1'),(4, '4', '4', null,'1')

i want this output to display all item either exit or not in a Bigbalprd table..

Description Section Catagory QTY
A HR India 1
B Baby INDIA(Cut) 0
C Ladies Pakistan 1
D Null Pakistan(Cut) 2
    Total 4

here i tried but it is not retreive all items, it just retreiving those item which are exit in both table here is my query  

-- Insert statements for procedure here
SELECT ItemMasterFile.Descriptionitem as Artical,Sections.Secnam as Section,Sum(Bigbalprd.Bpqty) as QTY,Catagory.CName as Catagory 
FROM Bigbalprd 
right JOIN ItemMasterFile ON Bigbalprd.CodeItem = ItemMasterFile.CodeItem 
right JOIN Sections ON Sections.SecID = Bigbalprd.SecID 
INNER JOIN Catagory ON Catagory.CID = Bigbalprd.CID
-- where Bigbalprd.Entrydate between @Startdate and @Enddate
group by ItemMasterFile.Descriptionitem,Catagory.CID,Catagory.CName,Sections.Secnam
order by catagory.CName
You are viewing reply posted by: pandeyism 5 months ago.
Posted 5 months ago Modified on 5 months ago

Hi akhter,

Refer below sample query.

SQL

CREATE TABLE #Catagory (CID INT, CName VARCHAR(MAX))
CREATE TABLE #Sections (SecID INT, Secnam VARCHAR(50))
CREATE TABLE #ItemMasterFile(CodeItem INT, Descriptionitem VARCHAR(max),SecID INT,CID INT)
CREATE TABLE #Bigbalprd(BID INT, CodeItem INT, SecID INT,  CID INT , QTY INT)

INSERT INTO #Catagory VALUES(1, 'INDIA')
INSERT INTO #Catagory VALUES(2, 'INDIA(Cut)')
INSERT INTO #Catagory VALUES(3, 'Pakistan')
INSERT INTO #Catagory VALUES(4, 'Pakistan(Cut)')

INSERT INTO #Sections VALUES(1, 'HR')
INSERT INTO #Sections VALUES(2, 'Baby')
INSERT INTO #Sections VALUES(3, 'Ladies')
INSERT INTO #Sections VALUES(4, 'Mix Rammage')

INSERT INTO #ItemMasterFile VALUES(1, 'A', '1', '1')
INSERT INTO #ItemMasterFile VALUES(2, 'B', '2', '2')
INSERT INTO #ItemMasterFile VALUES(3, 'C', '3', '3')
INSERT INTO #ItemMasterFile VALUES(4, 'D', '4',  null)
 
INSERT INTO #Bigbalprd VALUES(1, '1', '1', '1','1')
INSERT INTO #Bigbalprd VALUES(2, '3', '3', '3','1')
INSERT INTO #Bigbalprd VALUES(3, '4', '4', null,'1')
INSERT INTO #Bigbalprd VALUES(4, '4', '4', null,'1')

SELECT DISTINCT i.Descriptionitem,s.Secnam,c.CName,ISNULL(SUM(b.QTY),0)QTY
FROM #ItemMasterFile i
LEFT OUTER JOIN #Bigbalprd b ON  i.CodeItem = b.CodeItem
INNER JOIN #Sections s ON i.SecID = s.SecID
INNER JOIN #Catagory c ON c.CID = i.CodeItem
GROUP BY i.Descriptionitem,s.Secnam,c.CName

DROP TABLE #Catagory
DROP TABLE #Sections
DROP TABLE #ItemMasterFile
DROP TABLE #Bigbalprd

Output

Descriptionitem Secnam CName QTY
A HR INDIA 1
B Baby INDIA(Cut) 0
C Ladies Pakistan 1
D Mix Rammage Pakistan(Cut) 2