If Logic in a SQL Statement

Question

What I'm trying to do should be very simple but somehow I can't reach the right answer to my problem. I've asked something similar in the past but the answer given to me previously no longer fits the requirements. So here's what's going on - I need to conditionally select values from a table in my database in a different than the usual manner, like so:

Table:

• Id int (not null)
• ParentId int (not null)
• EventOn DateTime (not null)
• User int (null)

By the following select:

SELECT RST.* FROM RangeSheetTime RST
WHERE RST.[User] is not null

(in the above case, I take all the rows where the user isn't null)

Select RST.* FROM RangeSheetTime RST
WHERE RST.[User] is null

(in the above case, I take all the rows where the user is null)

So what am I trying to do? I want to build a select statement that when given a condition, such as EventOn < GETDATE(), will retrieve all the rows where the USER isn't null. In case there aren't any rows where USER isn't null, then it should retrieve the rows where it is null, if any.

How can I put this to work?

Note: I can't use if here, otherwise this would be easier.

EDIT:

I'm going to try to explain it the best I can. Imagine I have 3 rows for the same ParentId, 31. 2 of these rows have a column named StartOrEnd set to 1. There's just a difference between them, for the 1st one, the USER column is null; for the 2nd one, the USER column has the value 90. The 3rd row has the column StartOrEnd set to 0. Now, I want to display results no matter the value of startorend. But there's a catch. For every startorend, if there are more than 1 row and one of them has USER set to null and the others not null, then only the non null rows for that startorend will display. but in case there are no non null rows for this condition, than the null values will display. I hope I was clear now.

Answer 1

You should look into the CASE...WHEN construct, which is the equivalent of IF...THEN in SQL:

http://articles.techrepublic.com.com/5100-10878_11-5078041.html

Answer 2

If using sqlserver, look at the case statement, it might do what you want. select a,b, case when c is not null then 'The not null condition' else 'The null condition end [C] from Table T where condition

If using oracle, I think you can use decode.

Cheers.

Answer 3

This may work:

SELECT RST.* FROM RangeSheetTime RST
WHERE RST.[User] IS NOT NULL

UNION ALL

SELECT RST.* FROM RangeSheetTime RST
WHERE RST.[User] IS NULL
AND NOT EXISTS (
    SELECT Id FROM RangeSheetTime
    WHERE [User] IS NOT NULL
)

I have no means of testing this at the moment, so no guarantees.

Answer 4

Hallaghan, Are you asking about retrieval criteria or manipulating/ display of SQL result, if you are asking about manipulating or display of result case would help you else Union looks good to me.

Answer 5

id , parentid User , StartOrEnd

1 , 31, null , 1

2 , 31, 90 , 1

3 , 31, null , 0

and you want to say now, if startorEnd has multiple value for one parent id, you only want to see the row where user is not null , else the row with null user will come.

if it is like this, the answer given by Jeff should work

Answer 6

The final solution to this problem wasn't any easy to get at but after mashing our heads against a wall, me and a team mate got it down. Although every post from this topic helped us, none could really obtain the values we wanted, there were always flaws, but we are very thankful for all the help provided, without it we'd hardly have reached the answer by now.

So the solution is as follows:

select 
            P.ParentId RangeSheet
            ,coalesce(max(P.[End]), max(P.Start)) EventOn
            ,case when isnull(max(P.[End]), 0) = 0 then 1 else 0 end StartOrEnd
        from 
        (
            select 
                RST.ParentId
                ,case RST.StartOrEnd when 1 then RST.EventOn else null end Start
                ,case RST.StartOrEnd when 0 then RST.EventOn else null end [End]
            from 
            (
                select 
                    coalesce(MAN.ParentId, AUT.ParentId) ParentId
                    ,coalesce(MAN.StartOrEnd, AUT.StartOrEnd) StartOrEnd
                    ,coalesce(max(MAN.CreatedOn), max(AUT.CreatedOn)) CreatedOn
                from
                (
                    -- Obter os manuais
                    select 
                        RST.ParentId
                        ,RST.StartOrEnd
                        ,MAX(RST.CreatedOn) CreatedOn
                    from RangeSheetTime RST
                    where RST.[User] is not null
                    group by RST.ParentId, RST.StartOrEnd
                ) MAN
                full outer join
                (
                    -- Obter os automáticos
                    select 
                        RST.ParentId
                        ,RST.StartOrEnd
                        ,MAX(RST.CreatedOn) CreatedOn
                    from RangeSheetTime RST
                    where RST.[User] is null
                    group by RST.ParentId, RST.StartOrEnd
                ) AUT on MAN.ParentId=AUT.ParentId and MAN.StartOrEnd=AUT.StartOrEnd
                group by coalesce(MAN.ParentId, AUT.ParentId), coalesce(MAN.StartOrEnd, AUT.StartOrEnd)
            ) FOJ
            inner join RangeSheetTime RST on FOJ.ParentId=RST.ParentId and FOJ.StartOrEnd=RST.StartOrEnd and FOJ.CreatedOn=RST.CreatedOn
        ) P
        group by P.ParentId