CODEWITHSUNDEEP
sqlsql-server
Just_Stacking
Just_Stacking

Reputation: 405

MS-SQL select rows that have no records as null

I have the following 3 tables:

A) Unit information [Unit]
+-----------+---------+-------+
| record_ID | SN      | Data1 |
+-----------+---------+-------+
|     1     | 123 123 |  info |
+-----------+---------+-------+

B) Test related information [TestingData]
+---------+------------------+-----------+
| SN      | Info1            | Data1     |
+---------+------------------+-----------+
| 123 123 | Some information | Some data |
+---------+------------------+-----------+

C) Join table to more testing information [Link]
+--------+---------+
| LinkID | SN      |
+--------+---------+
|    1   | 123 123 |
+--------+---------+

D) Testing details [Tests]
+--------+---------------------+-----------+
| LinkID | testdate            | Testname  |
+--------+---------------------+-----------+
|    1   | 10.05.2015 22:22:00 | AD1       |
+--------+---------------------+-----------+

Now the tricky part:

I need to get the last record for each LinkID where test name = AD1 for example.

I managed to create a query that does just this, except that it returns no rows in case there was no AD1 named test:

SELECT * FROM (
SELECT
ROW_NUMBER() OVER(PARTITION BY rf2.linkID ORDER BY rf2.testDate DESC) AS rn 
,rf2.*, rs.*
FROM dbo.Unit rs 
FULL OUTER JOIN dbo.TestingData rf ON ( rs.SN = rf.SN  )  
FULL OUTER JOIN dbo.Link rf1 ON ( rf.SN = rf1.SN)  
FULL OUTER JOIN dbo.Tests rf2 ON ( rf1.linkID = rf2.linkID )  
WHERE rf2.testType = 'AD1'   
) T
WHERE rn = 1
ORDER BY SN ASC;

How can I extend (if possible at all) above query so that I will also get rows with null values in case there has not been a testType of AD1?

Reason behind this is that I will need to integrate this part to a larger report, which I will integrate through a join on SN.

Upvotes: 1

Views: 81

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I need to get the last record for each LinkID where test name = AD1 for example.

I can't figure out why your question has references to four tables, when one table has all the information you need. The full joins are even less explanable.

This gets the rows from tests that you want:

select top (1) with ties t.*
from tests t
where t.testName = 'AD1' 
order by row_number() over (partition by t.linkid order by t.testdate desc)
union all
select t.*
from tests t
where not exists (select 1 from tests t2 where t2.linkid = t.linkid and t.testName = 'AD1');

You can also do this with window functions:

select t.*
from (select t.*,
             row_number() over (partition by linkid, testname order by testdate desc) as seqnum,
             sum(case when testName = 'AD1' then 1 else 0 end) over (partition by linkid) as num_ad1
      from tests t
     ) t
where (l.num_ad1 > 0 and testName = 'AD1' and seqnum = 1) or
      (l.num_ad1 = 0);

You can JOIN in the other tables, for additional columns you may want. There is no need for FULL JOIN, a LEFT JOIN should suffice.

Upvotes: 1

Shushil Bohara
Shushil Bohara

Reputation: 5656

You are saying where test name = AD1 so I think you should check Testname instead of testType in the WHERE clause as below:

SELECT * FROM (
SELECT
ROW_NUMBER() OVER(PARTITION BY rf2.linkID ORDER BY rf2.testDate DESC) AS rn 
,rf2.*, rs.*
FROM dbo.Unit rs 
FULL OUTER JOIN dbo.TestingData rf ON ( rs.SN = rf.SN  )  
FULL OUTER JOIN dbo.Link rf1 ON ( rf.SN = rf1.SN)  
FULL OUTER JOIN dbo.Tests rf2 ON ( rf1.linkID = rf2.linkID )  
WHERE rf2.Testname = 'AD1'   -- You should use `Testname` instead of `testType`
) T
WHERE rn = 1
ORDER BY SN ASC;

OUTPUT:

rn  LinkID  testdate                Testname    record_ID   SN      Data1
1   1       2015-10-05 22:22:00.000 AD1         1           123 123 info

Upvotes: 0

Related Questions