Unique sql request

  • Hi experts, I have a very unique sql request here. I have 2 tables

    create table #Customer

    (

    CustomerName varchar(100),

    ServiceID int,

    Rendered bit

    )

    insert into #Customer

    values

    ('John',1, 1),

    ('John',2, 1),

    ('John',3, 1),

    ('James',1, 1),

    ('James',2, 1),

    ('James',3, 0)

    create table #Services

    (

    ServiceID int,

    ServiceName varchar(100)

    )

    insert into #Services

    values

    (1,'TeethCleaning'),

    (2,'EyeCleaning'),

    (3,'Massage')

    select * from #services

    select * from #Customer

    select * from #Customer c join #Services s on c.ServiceID = s.ServiceID

    Here is the problem,

    customer john had all 3 services rendered to him, customer james had 2 of 3 services rendered, what would be a clean optimal

    way of writing a sql query to return only customers that had all 3 services rendered only.

    So the expected result of the query would be only John and all the services rendered to him

    --Expected Values

    select * from

    (

    values

    ('John',1,1,'TeethCleaning'),

    ('John',2,1,'EyeCleaning'),

    ('John',3,1,'Massage')

    )as ExpectedResults (CustomerName, ServiceID, Rendered, ServiceName)

  • How abt this?

    ; with allServicesRenderedCustomers As

    (

    select c.CustomerName

    from #Customer c

    group by

    c.CustomerName

    HAVING AVG ( CAST( c.Rendered as decimal(4,2))) = 1.00

    )

    select *

    from #Customer c

    join allServicesRenderedCustomers a

    on a.CustomerName = c.CustomerName

    join #Services s

    on c.ServiceID = s.ServiceID

  • Or

    SELECT C.CustomerName,C.ServiceID,C.Rendered ,S.ServiceName

    FROM #Customer C

    Inner JOIN #Services S ON S.ServiceID = C.ServiceID

    WHERE CustomerName IN (

    SELECT CustomerName FROM #Customer

    WHERE Rendered = 1

    GROUP BY CustomerName

    HAVING COUNT (CustomerName) = (SELECT COUNT(*) FROM #Services))

  • Both great solutions, I appreciate them. Seems like there is almost no way to get around using the "Having" clause. Interesting.

  • Using the HAVING clause is the most efficient way of doing it, but there are other ways.

    Are you required to not use the HAVING clause?

  • Oh not at all, I can do it any way I like, it was just something I noticed that both solutions had in common. Thanks guys

  • Probably not very performant, but you could always use:

    ;with customer_name as

    (

    SELECT *,SUM(rendered) OVER(PARTITION BY customername) as totalrendered from #customer

    )

    SELECT

    customer.customername

    ,customer.serviceid

    ,serv.servicename

    FROM customer_name customer

    INNER JOIN #services serv on serv.serviceid = customer.serviceid

    WHERE totalrendered = 3

    which manages to avoid using the HAVING clause!

  • if you need to avoid the having you can do this... not sure if it's the most efficient... (using the limited data it's 2 ms and 6 scans and 6 reads better than the having clause)

    select *

    from

    (

    select

    customername,

    rendered = MIN(cast(rendered as int))

    from

    #Customer c

    group by

    customername) as c,

    #Services s

    where

    c.rendered <> 0

  • Davin21 (6/9/2011)


    Probably not very performant, but you could always use:

    ;with customer_name as

    (

    SELECT *,SUM(rendered) OVER(PARTITION BY customername) as totalrendered from #customer

    )

    SELECT

    customer.customername

    ,customer.serviceid

    ,serv.servicename

    FROM customer_name customer

    INNER JOIN #services serv on serv.serviceid = customer.serviceid

    WHERE totalrendered = 3

    which manages to avoid using the HAVING clause!

    could be my settings, but I get the error "Operand data type bit is invalid for sum operator."

  • SUM(CONVERT(INT, rendered))

  • Sorry didn't spot it was a BIT! As Ninja's said, casting / converting to INT should do the trick

  • The reason it's slow is because you pretty much always have to scan the whole table to figure out if all the matches are done. Unless and until you find a way around that you're pretty much screwed... having or not having won't play much in there.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply