CODEWITHSUNDEEP
sqlsql-server
simenh
simenh

Reputation: 58

SQL Server : perform an IF test to see if a row exist

I have a table like this:

[invoice_lines]:

id (int), customerid (int), price (int), productname (text)

I want to query this table, and extract all rows. But I want an additional column which can be a 1/0 column, which checks whether or not there exists a row where productname = 'SLA' AND customerid = customerid.

So lets say I have a row which looks like this:

id    customerid    price     productname
-----------------------------------------
 1       134         125      Internet

I want this row to be extracted, but I want to see if there is any rows with the productname = 'SLA' AND customerid = '134'. If there is a row which meets these criterias, I want the additional column (lets call it SLA) to have the value 1. If it does not exist, I would like the value to be 0.

Any pointers?

Upvotes: 1

Views: 59

Answers (5)

Dan Guzman
Dan Guzman

Reputation: 46202

For all invoice lines in the table, I suggest a CASE expression around a windowing function. A composite clustered/covering index with a key of customerid and productname to provide the best performance.

SELECT  id
      , customerid
      , price
      , productname
      , CASE SUM(CASE WHEN productname = 'SLA' THEN 1
                      ELSE 0
                 END) OVER ( PARTITION BY customerid )
          WHEN 0 THEN 0
          ELSE 1
        END AS SLA
FROM    invoice_lines il;

A correlated subquery can also do the job but there will probably be a tipping point where the execution plan changes to handle a large number of invoiced lines.

SELECT  id
      , customerid
      , price
      , productname
      , CASE WHEN EXISTS ( SELECT   *
                           FROM     dbo.invoice_lines b
                           WHERE    b.customerid = a.customerid
                                    AND b.productname = 'SLA' ) THEN 1
             ELSE 0
        END AS SLA
FROM    dbo.invoice_lines AS a;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You should just use exists in the select. Or, use conditional aggregation with a window function. This is probably the most efficient method:

select il.*, 
       (case when sum(case when productname = 'SLA' then 1 else 0 end) over (partition by customerid)
             then 1
             else 0
        end) as sla
from invoice_lines il;

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

I would do this with CROSS APPLY because joins may produce duplicate rows:

SELECT  *
FROM    TableName t1
CROSS APPLY ( SELECT  CASE WHEN EXISTS ( SELECT   *
                                         FROM     TableName t2
                                         WHERE    t2.customerid = t1.customerid AND t2.productname = 'SLA' )
                           THEN 1
                           ELSE 0
                      END AS SLA
             ) a

Upvotes: 0

juergen d
juergen d

Reputation: 204756

select t1.*, 
       case when t2.customerid is not null 
            then 1 
            else 0 
       end as result_check
from invoice_lines t1
left join invoice_lines t2 on t1.customerid = t2.customerid 
                          and t2.productname = 'SLA'

Upvotes: 3

Nightmaresux
Nightmaresux

Reputation: 538

You can use case on your new column which will check that :

 select id, customerid,price,productname
    case when productname = 'SLA' and customerid = '134' then 1 else 0 end as SLA
    from [invoice_lines]

Upvotes: 0

Related Questions