query question

[saadabc]

I have a table in sql server as follows:


table
-----

c_id unique_id text issue_type
---- --------- ---- ------

4 1 service changed ... servicechange

4 2 validation for service change validation

5 3 service changed servicechange

5 4 validation for service change validation

6 5 service changed servicechange

7 6 some other text ......


i want to pull service changed row only if it has a validation row for it. validation row could be immediately following the service change row .... or it could be some rows after the service change row... and also it may be absent for a particular service change row.


i can pull the service changed rows like this:

select * from table where issue_type = 'validation'

 

[gmmastros]

It's hard to tell if this will work for you, but I'm reasonably sure it's in the 'right direction'

Select A.*
From   Table A
       Inner join Table B 
         On  A.c_id = B.c_id
Where  A.issue_type = 'validation'
       And B.issue_type = 'servicechange'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom