T-sql case returns wrong value

问题:

I'm struggling to get my SQL query to work. When my IN FAILS to find a row I want to return a row just like the ones in the picture but I want the ifuserholdscertificate to be set to 'NO'.

SELECT tuc.id,
       tu.firstName,
       tuc.uid,
       tuc.value,
       tc.name,
       count(tuc.value) over (PARTITION BY tuc.uid) AS 'amount',
       'certificate DESIRABLE' AS 'typeofthing' ,
       'YES' AS 'HasorNot',
       ifuserholdscertificate = CASE
                                    WHEN count(tuc.value) = 0 THEN 'NO'
                                    ELSE 'YES'
                                END
FROM t_user_certificates tuc,
     t_certificates tc,
     t_users tu
WHERE tuc.value IN (4,
                    12,
                    31)
  AND tuc.value = tc.id
  AND tu.id = tuc.uid
GROUP BY tuc.id,
         tu.firstName,
         tuc.uid,
         tuc.value,
         tc.name

This is the data the query generates!

As you can see even if some people only get 2 in the amount row it will still not fetch a row and set ifuserholdscertificate to 'NO'.

UPDATED PER REQUEST!

select  tuc.id,
count(tuc.value) as 'counten',
tu.firstName,
tuc.uid,
tuc.value, 
tc.name, 
count(tuc.value) over (PARTITION BY tuc.uid) as 'amount',
'certificateDESIRABLE'  as 'typeofthing' ,
'YES' as 'HasorNot',
HasOrders = CASE
                WHEN count(tuc.value) = 0 THEN 'NO'
                ELSE 'YES'
            END
from t_user_certificates tuc                   
left outer join t_certificates tc
on tuc.value = tc.id
left outer join t_users tu
on tu.id = tuc.uid
GROUP BY tuc.id, tu.firstName, tuc.uid, tuc.value, tc.name

Alwyas one 1 in the count and always 'YES'

回答1:

This might be more like what you're looking for. You're always returning 1, and no users w/o certificates because you have the IN bit in the where clause. So even with an outer join, you only return rows from t_user_certificates with those values. It effectively becomes an inner join. ANSI join syntax is your friend. It separates JOIN logic from filters.

SELECT tuc.id,
       tu.firstName,
       tuc.uid,
       tuc.value,
       tc.name,
       count(tuc.value) AS 'amount',
       'certificate DESIRABLE' AS 'typeofthing' ,
       'YES' AS 'HasorNot',
       ifuserholdscertificate = CASE
                                    WHEN count(tuc.value) > 0 THEN 'YES'
                                    ELSE 'NO'
                                END
FROM
    t_users tu
LEFT JOIN t_user_certificates tuc
    ON
    tu.id = tuc.uid
    AND 
    tuc.value IN
    (
        4
        , 12
        , 31
    )
LEFT JOIN
     t_certificates tc
    ON
    tuc.value = tc.id

GROUP BY tuc.id,
         tu.firstName,
         tuc.uid,
         tuc.value,
         tc.name;

回答2:

You may be getting nulls instead of zeros you are not accounting for. I would try this instead:

ifuserholdscertificate = CASE                                  
    WHEN (count(tuc.value) = 0 or count(tuc.Value) = null) THEN 'NO'
    ELSE 'YES'
    END

I am not getting why you are stating that people get 2 though and then accounting for zero and expecting a 'NO' answer. Without a sense of the source data you are collecting it is hard to know what the data looks like before referencing it. You are using a windowed function as well ( the over(partion by..) ) and that will give different data than a regular count function will. If your goal is to use a case when expression on a windowed function you would have to do a nested select or cte to get the numbers first and then do logic on them later.

Something like this:

declare @Person Table ( personID int identity, person varchar(8));

insert into @Person values ('Brett'),('Sean'),('Chad');

declare @Orders table ( OrderID int identity, PersonID int, Desciption varchar(32), Amount int);

insert into @Orders values (1, 'Shirt', 20),(2, 'Shirt', 22),(2, 'Shoes', 52);

With a as 
    (
    Select 
        p.person
    ,   count(o.orderID) over(partition by person) as Count
    from @Person p
        left join @Orders o on p.personID = o.PersonID
    )
select
    person
,   case when Count = 0 then 'No' else cast(Count as varchar(8)) end as Count
from a

回答3:

Add a column to your query to debug. And show those results with your screen shot.

MyCOUNT = count(tuc.value),

My Northwind examples works OK. When there are no Orders (rows) for a parent Customer, I get "No".

................

Use Northwind
GO

Select 
    custs.CustomerID,
    MyCOUNT = count(ords.OrderID),
    HasOrders = CASE
        WHEN count(ords.OrderID) = 0 THEN 'NO'
        ELSE 'YES'
    END
from 
    [dbo].[Customers] custs left outer join [dbo].[Orders] ords on custs.CustomerID = ords.CustomerID
GROUP BY 
    custs.CustomerID
order by 2

Here are some additional queries to show what is going on:

Select Label = 'You get no results here', custs.CustomerID , ords.OrderID 
from 
    [dbo].[Customers] custs join [dbo].[Orders] ords on custs.CustomerID = ords.CustomerID
Where
    ords.OrderID IS NULL


Select Label = 'You get Customers with no Orders here', custs.CustomerID , ords.OrderID 
from 
    [dbo].[Customers] custs left outer join [dbo].[Orders] ords on custs.CustomerID = ords.CustomerID
Where
    ords.OrderID IS NULL


Select Label = 'You get Customers with or without Orders here', custs.CustomerID , ords.OrderID 
from 
    [dbo].[Customers] custs left outer join [dbo].[Orders] ords on custs.CustomerID = ords.CustomerID

Here's where you can get the pubs and Northwind database. This is helpful because most developer already have these databases installed (and AdventureWorks) for working out situations like this.

http://msdn.microsoft.com/en-us/library/ms143221(v=sql.105).aspx