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.Query to select records with NULL values using Join in SQL Server<p>Hi <a class="username" rel="smile"> smile</a>,</p> <p>You have to use sub query. With join it is not possible.</p> <p>Check the below query.</p> <p><strong><span style="text-decoration: underline;">SQL</span></strong></p> <pre class="brush: sql">DECLARE @Date VARCHAR(10) SET @Date = &#39;13.08.2019&#39; DECLARE @tblStudent AS TABLE(AdmissionNo VARCHAR(20),SName VARCHAR(10),FName VARCHAR(10),Phone INT) INSERT INTO @tblStudent VALUES(&#39;R-01&#39;,&#39;ABC&#39;,&#39;XYZ&#39;,123456789) INSERT INTO @tblStudent VALUES(&#39;R-02&#39;,&#39;SKY&#39;,&#39;ABC&#39;,123456789) INSERT INTO @tblStudent VALUES(&#39;R-03&#39;,&#39;XYZ&#39;,&#39;SKY&#39;,123456789) &nbsp; DECLARE @tblAttendance AS TABLE(AdmissionNo VARCHAR(20),ADate VARCHAR(10),CheckIn VARCHAR(10),CheckOut VARCHAR(10),Remarks VARCHAR(10)) INSERT INTO @tblAttendance VALUES(&#39;R-01&#39;,&#39;12.08.2019&#39;,&#39;8:00 AM&#39;,&#39;1:00 PM&#39;,&#39;Present&#39;) INSERT INTO @tblAttendance VALUES(&#39;R-02&#39;,&#39;12.08.2019&#39;,&#39;8:00 AM&#39;,&#39;1:00 PM&#39;,&#39;Present&#39;) INSERT INTO @tblAttendance VALUES(&#39;R-03&#39;,&#39;12.08.2019&#39;,&#39;8:00 AM&#39;,&#39;1:00 PM&#39;,&#39;Present&#39;) INSERT INTO @tblAttendance VALUES(&#39;R-01&#39;,&#39;13.08.2019&#39;,&#39;8:00 AM&#39;,&#39;1:00 PM&#39;,&#39;Present&#39;) &nbsp; SELECT * FROM ( SELECT c.AdmissionNo,c.SName, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (SELECT o.ADate FROM @tblAttendance as o WHERE o.AdmissionNo = c.AdmissionNo AND o.ADate = @Date) ADate, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (SELECT o.CheckIn FROM @tblAttendance as o WHERE o.AdmissionNo = c.AdmissionNo AND o.ADate = @Date) CheckIn, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (SELECT o.CheckOut FROM @tblAttendance as o WHERE o.AdmissionNo = c.AdmissionNo AND o.ADate = @Date) CheckOut, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (SELECT o.Remarks FROM @tblAttendance as o WHERE o.AdmissionNo = c.AdmissionNo AND o.ADate = @Date) Remarks FROM @tblStudent as c WHERE c.AdmissionNo IS NOT NULL) t WHERE t.Remarks IS NULL</pre> <p><strong><span style="text-decoration: underline;">Output</span></strong></p> <table border="1"> <tbody> <tr><th>AdmissionNo</th><th>SName</th><th>ADate</th><th>CheckIn</th><th>CheckOut</th><th>Remarks</th></tr> <tr> <td>R-02</td> <td>SKY</td> <td><em>NULL</em></td> <td><em>NULL</em></td> <td><em>NULL</em></td> <td><em>NULL</em></td> </tr> <tr> <td>R-03</td> <td>XYZ</td> <td><em>NULL</em></td> <td><em>NULL</em></td> <td><em>NULL</em></td> <td><em>NULL</em></td> </tr> </tbody> </table>https://www.aspforums.net:443/Threads/176006/Query-to-select-records-with-NULL-values-using-Join-in-SQL-Server/https://www.aspforums.net:443/Threads/176006/Query-to-select-records-with-NULL-values-using-Join-in-SQL-Server/Wed, 14 Aug 2019 06:59:26 GMT