ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Select all records from multiple table using Where clause in SQL Server<p>Hi&nbsp;akhter,</p> <p>Your query is wrong.</p> <p>1. GROUP BY clause should be after the WHERE statement.</p> <p>2. Your&nbsp;WHERE statement is missing BETWEEN clause.</p> <p>If you want to display the record having date as null, you need to add the&nbsp;IS NULL checking with OR condition for Date column.</p> <p>Check this sample query. Now please take its reference and correct your query as per your table structure.</p> <p><strong><span style="text-decoration: underline;">SQL</span></strong></p> <pre class="brush: 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,&#39;INDIA&#39;) INSERT INTO #Catagory VALUES(2,&#39;INDIA(Cut)&#39;) INSERT INTO #Catagory VALUES(3,&#39;Pakistan&#39;) INSERT INTO #Catagory VALUES(4,&#39;Pakistan(Cut)&#39;) INSERT INTO #Sections VALUES(1,&#39;HR&#39;) INSERT INTO #Sections VALUES(2,&#39;Baby&#39;) INSERT INTO #Sections VALUES(3,&#39;Ladies&#39;) INSERT INTO #Sections VALUES(4,&#39;Mix Rammage&#39;) INSERT INTO #ItemMasterFile VALUES(1,&#39;A&#39;,1,1) INSERT INTO #ItemMasterFile VALUES(2,&#39;B&#39;,2,2) INSERT INTO #ItemMasterFile VALUES(3,&#39;C&#39;,3,3) INSERT INTO #ItemMasterFile VALUES(4,&#39;D&#39;,4,null) INSERT INTO #ItemMasterFile VALUES(5,&#39;e&#39;,5,null) INSERT INTO #ItemMasterFile VALUES(6,&#39;f&#39;,6,null) INSERT INTO #ItemMasterFile VALUES(7,&#39;g&#39;,4,2) INSERT INTO #ItemMasterFile VALUES(8,&#39;h&#39;,4,3) INSERT INTO #ItemMasterFile VALUES(9,&#39;K&#39;,2,2) INSERT INTO #Bigbalprd VALUES(1,1,1,1,1,&#39;01-06-2019&#39;) INSERT INTO #Bigbalprd VALUES(2,3,3,3,1,&#39;02-06-2019&#39;) INSERT INTO #Bigbalprd VALUES(3,4,4,null,1,&#39;03-06-2019&#39;) INSERT INTO #Bigbalprd VALUES(4,4,4,null,1,&#39;04-06-2019&#39;) DECLARE @StartDate DATETIME, @Enddate DATETIME SET @StartDate = &#39;01-06-2019&#39; SET @Enddate = &#39;03-06-2019&#39; SELECT DISTINCT i.Descriptionitem,s.Secnam,c.CName,&nbsp; ISNULL(SUM(b.QTY),0)QTY FROM #ItemMasterFile i LEFT OUTER JOIN #Bigbalprd b ON&nbsp; 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</pre> <p><strong><span style="text-decoration: underline;">Output</span></strong></p> <table border="1"> <tbody> <tr style="height: 15.0pt;"> <td style="height: 15.0pt; width: 48pt;" width="64" height="20"><strong>Descriptionitem</strong></td> <td style="width: 48pt;" width="64"><strong>Secnam</strong></td> <td style="width: 48pt;" width="64"><strong>CName</strong></td> <td style="width: 48pt;" width="64"><strong>QTY</strong></td> </tr> <tr style="height: 15.0pt;"> <td style="height: 15.0pt;" height="20">A</td> <td>HR</td> <td>INDIA</td> <td align="right">1</td> </tr> <tr style="height: 15.0pt;"> <td style="height: 15.0pt;" height="20">B</td> <td>Baby</td> <td>INDIA(Cut)</td> <td align="right">0</td> </tr> <tr style="height: 15.0pt;"> <td style="height: 15.0pt;" height="20">C</td> <td>Ladies</td> <td>Pakistan</td> <td align="right">1</td> </tr> <tr style="height: 15.0pt;"> <td style="height: 15.0pt;" height="20">D</td> <td>Mix Rammage</td> <td>Pakistan(Cut)</td> <td align="right">1</td> </tr> <tr style="height: 15.0pt;"> <td style="height: 15.0pt;" height="20">e</td> <td>NULL</td> <td>NULL</td> <td align="right">0</td> </tr> <tr style="height: 15.0pt;"> <td style="height: 15.0pt;" height="20">f</td> <td>NULL</td> <td>NULL</td> <td align="right">0</td> </tr> <tr style="height: 15.0pt;"> <td style="height: 15.0pt;" height="20">g</td> <td>Mix Rammage</td> <td>NULL</td> <td align="right">0</td> </tr> <tr style="height: 15.0pt;"> <td style="height: 15.0pt;" height="20">h</td> <td>Mix Rammage</td> <td>NULL</td> <td align="right">0</td> </tr> <tr style="height: 15.0pt;"> <td style="height: 15.0pt;" height="20">K</td> <td>Baby</td> <td>NULL</td> <td align="right">0</td> </tr> </tbody> </table>https://www.aspforums.net:443/Threads/718012/Select-all-records-from-multiple-table-using-Where-clause-in-SQL-Server/https://www.aspforums.net:443/Threads/718012/Select-all-records-from-multiple-table-using-Where-clause-in-SQL-Server/Wed, 19 Jun 2019 07:40:01 GMT