CODEWITHSUNDEEP
t-sql
90abyss
90abyss

Reputation: 7347

TSQL: Return only if 1 row exists based on where clause

Lets say I have a table with 3 columns with following values:

   ticketid     indexid    type  
    ---          ---       ---
    100           191       0
    100           192       2
    100           193       4
    200           194       0
    300           195       1
    300           196       0

My desired output: 

   ticketid     indexid    type  
    ---          ---       ---
    200           194       0

I only want those rows which have:

1) only 1 row (based on ticketid) in the table

2) have type = (0,2,4)

This is the query I have which is not working:

select distinct ticketid from tab1 where type not in (1,3) group by ticketid having count(indexid) = 1

When I run the above query I am still getting tickets which have more than 1 rows. How can I fix this?

Upvotes: 2

Views: 4192

Answers (4)

jveazey
jveazey

Reputation: 5458

You can do the grouping in a subquery. Then in the main query pull all of the columns.

select ticketid, indexid, type
  from tab1
 where type in (0,2,4)
   and ticketid in (
    select ticketid from tab1 group by ticketid having count(*) = 1
)

Upvotes: 1

dfdsfdsfsdf
dfdsfdsfsdf

Reputation: 663

you need to group the data and do a count on each group. For ex)

create table tab1 
(
    ticketid int,
    indexid int,
    type int
)



insert into tab1
values 
( 100,           191,       0),
(100,           192,       2),
(   100,           193 ,      4),
(  200  ,        194   ,    0),
( 300  ,         195 ,      1),
(300  ,         196  ,     0)




select * 
from tab1 


select ticketid 
from tab1 
--exclude tickets that contains the invalid types
where ticketID NOT IN (

    --get tickets that does not contain the valid types
    select ticketID
    from tab1
    where type NOT IN (0,2,4)
)
group by ticketid 
having count(1) = 1

Upvotes: 1

EastOfJupiter
EastOfJupiter

Reputation: 781

I've edited this answer because I misread the original requirements.

DECLARE  @tab1 TABLE (TicketID INT, IndexID INT, Type Int)

INSERT 
  INTO   @tab1 (TicketID, IndexID, Type)
VALUES  (100,191,0)
       ,(100,192,2)
       ,(100,193,4)
       ,(200,194,0)
       ,(300,195,1)
       ,(300,196,0)


SELECT   T.TicketID
        ,T.IndexID
        ,T.Type
  FROM  (
         SELECT  TicketID
                ,COUNT(IndexID) AS CountOfIndex
                ,CASE WHEN Type IN (0,2,4) THEN 1 ELSE 0 END AS ValidType
           FROM  @tab1
          GROUP
             BY  TicketID
                ,CASE WHEN Type IN (0,2,4) THEN 1 ELSE 0 END 
        ) DATA1
  JOIN   @tab1 T
    ON   T.TicketID = DATA1.TicketID
 WHERE   DATA1.CountOfIndex = 1
GROUP BY T.TicketID
        ,T.IndexID
        ,T.Type
 HAVING MIN(DATA1.ValidType) = 1

This provides the following results:

TicketID    IndexID     Type
200         194         0

This query uses a derived table to first find the number of IndexID values based on the repeating TicketID values, while also determining if the Type column value is valid for inclusion in the final output.

The outer query then looks for a the Tickets that have a CountOfIndex = 1, and the minimum value for the Type = 1 (eliminating any records where the TicketID was associated with an invalid Type value).

It may not be the cleanest solution, but I believe that this code highlights the thinking behind classifying wanted and unwanted data, and how to filter the unwanted data out.

Upvotes: 1

RBarryYoung
RBarryYoung

Reputation: 56725

Assuming that ticketid+indexid is unique, you can do this:

select ticketid 
from tab1 
where type not in (1,3) 
group by ticketid 
having count(distinct indexid) = 1

(which is the just your original query reformatted and the distinct keyword moved to the correct place)

Upvotes: 1

Related Questions