Select all records from multiple table using Where clause in SQL Server

Last Reply 2 months ago By dharmendr

Posted 2 months ago

i add more item (E,F,G,K) these items are not getting display please have a look 

CREATE TABLE #Catagory (CID INT,CName VARCHAR(50))
CREATE TABLE #Sections (SecID INT,Secnam VARCHAR(50))
CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,CID INT)
CREATE TABLE #Bigbalprd(BID INT,CodeItem INT,SecID INT,CID INT,QTY INT,Entrydate DATE)
   
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 #ItemMasterFile VALUES(5,'e',4,null)
INSERT INTO #ItemMasterFile VALUES(6,'f',4,null)
INSERT INTO #ItemMasterFile VALUES(7,'g',4,2)
INSERT INTO #ItemMasterFile VALUES(8,'h',4,3)
INSERT INTO #ItemMasterFile VALUES(9,'K',2,2)
    
INSERT INTO #Bigbalprd VALUES(1,1,1,1,1,'01-06-2019')
INSERT INTO #Bigbalprd VALUES(2,3,3,3,1,'02-06-2019')
INSERT INTO #Bigbalprd VALUES(3,4,4,null,1,'03-06-2019')
INSERT INTO #Bigbalprd VALUES(4,4,4,null,1,'04-06-2019')
 
DECLARE @StartDate DATETIME, @Enddate DATETIME
SET @StartDate = '01-06-2019'
SET @Enddate = '03-06-2019'
 
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
WHERE (b.Entrydate BETWEEN @StartDate AND @Enddate) OR b.Entrydate IS NULL
GROUP BY i.Descriptionitem, s.Secnam, c.CName
 
DROP TABLE #Catagory
DROP TABLE #Sections
DROP TABLE #ItemMasterFile
DROP TABLE #Bigbalprd
Posted 2 months ago

Hi akhter,

Your query is wrong.

1. GROUP BY clause should be after the WHERE statement.

2. Your WHERE statement is missing BETWEEN clause.

If you want to display the record having date as null, you need to add the IS NULL checking with OR condition for Date column.

Check this sample query. Now please take its reference and correct your query as per your table structure.

SQL

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

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 #ItemMasterFile VALUES(5,'e',5,null)
INSERT INTO #ItemMasterFile VALUES(6,'f',6,null)
INSERT INTO #ItemMasterFile VALUES(7,'g',4,2)
INSERT INTO #ItemMasterFile VALUES(8,'h',4,3)
INSERT INTO #ItemMasterFile VALUES(9,'K',2,2)

INSERT INTO #Bigbalprd VALUES(1,1,1,1,1,'01-06-2019')
INSERT INTO #Bigbalprd VALUES(2,3,3,3,1,'02-06-2019')
INSERT INTO #Bigbalprd VALUES(3,4,4,null,1,'03-06-2019')
INSERT INTO #Bigbalprd VALUES(4,4,4,null,1,'04-06-2019')

DECLARE @StartDate DATETIME, @Enddate DATETIME
SET @StartDate = '01-06-2019'
SET @Enddate = '03-06-2019'

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
FULL OUTER JOIN #Sections s ON i.SecID = s.SecID
FULL OUTER JOIN #Catagory c ON c.CID = i.CodeItem
WHERE (b.Entrydate BETWEEN @StartDate AND @Enddate) OR b.Entrydate IS NULL
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) 1
e NULL NULL 0
f NULL NULL 0
g Mix Rammage NULL 0
h Mix Rammage NULL 0
K Baby NULL 0