CODEWITHSUNDEEP
sqlsql-server
Izikon
Izikon

Reputation: 902

Get only 1 result from SQL table (null or with date)

I have a table name ItemMedia with 2 rows

ID   Source    FromDate     ToDate
----------------------------------
122  122A.jpg  NULL         NULL
122  122B.jpg  1/1/14       1/2/14

I want to return only one row if today is not between the FromDate to the ToDate return the row with NULL values in 'FromDate' and 'ToDate'

I've tried this SQL statement

IF (SELECT ItemID FROM ItemsMedia 
WHERE (FromDate<=GETDATE() AND ToDate>=GETDATE())) IS NOT NULL
BEGIN
    SELECT Source FROM ItemsMedia 
WHERE (FromDate<=GETDATE() AND ToDate>=GETDATE())
END
ELSE
BEGIN
    SELECT Source FROM ItemsMedia 
    WHERE (FromDate IS NULL AND ToDate IS NULL)
END

Which is working but the the source is part of other related tables (ItemText, ItemDetails and more) and i need to return all of them as one result. So the Source i get from the statement is part of a larger SQL statement so i can get this sample end result

ID    Source    Text        Details
----------------------------------
122   122B.jpg  Some Text   Some Details

Upvotes: 0

Views: 84

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

If I understand correctly, you just want to return one row with some precedence rules. You can do that with top and order by:

SELECT top 1 im.*
FROM ItemsMedia im
ORDER BY (case when FromDate <= GETDATE() AND ToDate >= GETDATE()
               then 1
               when FromDate IS NULL AND ToDate IS NULL
               then 2
               else 3
          end);

Upvotes: 1

Related Questions