I would like to have two stored procedures get the 'matched' and 'unmatched' rows, respectively... from a single table...

'matched' rows are two rows that have a state of 1 and 2 respectively, for the same:

```
uID aID cID and pID

```

An 'unmatched' row is a row that has a state of 1, but there isn't another row with a state of 2 that has the same uID, aID, cID and pID

( I have removed the unique identifier Indx from the data for brevity )

***Sample data***

```
uID aID cID pID state   occurs
10  200 5000    1240    1   2018-04-17 08:12:13.367
80  542 9000    5700    1   2018-04-17 08:12:54.113
10  240 5000    3860    1   2018-04-17 08:13:09.817
10  200 5000    1240    2   2018-04-17 08:13:18.010
30  240 7000    5938    1   2018-04-17 08:13:31.510
80  542 9000    5700    2   2018-04-17 08:14:04.363

```

Here are examples of 'matched' rows

```
uID aID cID pID state   occurs
10  200 5000    1240    1   2018-04-17 08:12:13.367
10  200 5000    1240    2   2018-04-17 08:13:18.010
80  542 9000    5700    1   2018-04-17 08:12:54.113
80  542 9000    5700    2   2018-04-17 08:14:04.363

```

Here are examples of 'unmatched' rows

```
uID aID cID pID state   occurs
10  240 5000    3860    1   2018-04-17 08:13:09.817
30  240 7000    5938    1   2018-04-17 08:13:31.510

```

**Matched Row code**

Where I am having difficulty is wrapping my head around the statement that group the matched sets... I thought I would be able to do something like this to get both rows with 1 and 2 but no such luck....

```
select uID, aID, cID, pID, state
from Data where state in (1,2) 
group by uID, aID, cID, pID, state
having state = 2 and state = 1

```

**Unmatched Row code**

Then I thought I would be able to do something similar for the unmatched rows, but this doesn't work either....

```
select uID, aID, cID, pID, state
from Data where state in (1,2) 
group by uID, aID, cID, pID, state
having state != 2 and state = 1

```

I need some *help*... thanks

***Infrastructure***

Here is the code to create the table:

```
CREATE TABLE [dbo].[Data](
    [INDX] [uniqueidentifier] NOT NULL,
    [uID] [int] NOT NULL,
    [aID] [int] NOT NULL,
    [cID] [int] NOT NULL,
    [pID] [int] NOT NULL,
    [state] [int] NOT NULL,
    [occurs] [datetime] NOT NULL,
 CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED 
(
    [INDX] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

```

the stored procedure to "insert the data"

```
CREATE PROCEDURE [dbo].[InsertData]
    -- Add the parameters for the stored procedure here

    @userID int, 
    @appID int, 
    @compID int, 
    @procID int, 
    @state int,
    @occurence datetime

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here

        declare @indx as uniqueidentifier
        set @indx = NEWID();

        insert into [Data]
        Values(
                @indx,
                @userID,
                @appID,
                @compID,
                @procID,
                @state,
                @occurence
                )

END
GO

```

and here is some code to help populate the table:

```
declare @userID int
declare @appID int
declare @compID int
declare @procID int
declare @state int
declare @occurence datetime

set @userID = 10
set @appID = 200
set @compID = 5000
set @procID = 1240
set @state = 1

set @occurence = GETDATE();
EXEC InsertData @userID, @appID, @compID, @procID, @state, @occurence

```

Test when adding another state:

```
    set @userID = 80
    set @appID = 546
    set @compID = 9000
    set @procID = 5700
    set @state = 3
    set @occurence = GETDATE();
    EXEC InsertData @userID, @appID, @compID, @procID, @state, @occurence

```

So I wind up with this data set:

```
uID aID cID pID state
10  200 5000    1240    2
10  200 5000    1240    1
10  240 5000    3860    1
80  542 9000    5700    1
30  240 7000    5938    1
80  546 9000    5700    3
80  542 9000    5700    2

```

So... scsimon... your query works great... but what if this were the case:

**NEW DUPLICATE DATA ADDED**

```
uID aID cID pID state
10  200 5000    1240    1
80  542 9000    5700    1
10  240 5000    3860    1
10  200 5000    1240    2
30  240 7000    5938    1
80  542 9000    5700    2
80  546 9000    4502    3
10  200 5000    1240    1
10  200 5000    1240    2

```

Query results, which is close... 

```
uID aID cID pID state   occurs
10  200 5000    1240    1   2018-04-17 11:57:22.693
10  200 5000    1240    1   2018-04-17 11:57:29.797
10  200 5000    1240    2   2018-04-17 11:57:25.740
10  200 5000    1240    2   2018-04-17 11:57:30.827
80  542 9000    5700    1   2018-04-17 11:57:23.710
80  542 9000    5700    2   2018-04-17 11:57:27.767

```

But what I really want is this:

```
uID aID cID pID state   occurs
10  200 5000    1240    1   2018-04-17 11:57:22.693
10  200 5000    1240    2   2018-04-17 11:57:25.740

10  200 5000    1240    1   2018-04-17 11:57:29.797
10  200 5000    1240    2   2018-04-17 11:57:30.827

80  542 9000    5700    1   2018-04-17 11:57:23.710
80  542 9000    5700    2   2018-04-17 11:57:27.767

```

***Final Answer (thanks scsimon)***

'Matched'

```
select t.uID, t.aID, t.cID, t.pID, t.state, t.occurs
from Data t
inner join
    (select uID, aID, cID, pID
     from Data
     where state in (1,2) --optional if needed
     group by uID, aID, cID, pID
     having count(*) > 1) t2 on 
 t2.uID = t.uID
 and t2.aID = t.aID
 and t2.cID = t.cID
 and t2.pID = t.pID
 order by uID, occurs, state

```

Returns a set of :

```
uID aID cID pID state   occurs
10  200 5000    1240    1   2018-04-17 11:57:22.693
10  200 5000    1240    2   2018-04-17 11:57:25.740
10  200 5000    1240    1   2018-04-17 11:57:29.797
10  200 5000    1240    2   2018-04-17 11:57:30.827
80  542 9000    5700    1   2018-04-17 11:57:23.710
80  542 9000    5700    2   2018-04-17 11:57:27.767

```

'Unmatched'

```
select t.uID, t.aID, t.cID, t.pID, t.state, t.occurs
from Data t
inner join
    (select uID, aID, cID, pID
     from Data
     where state in (1,2) --optional if needed
     group by uID, aID, cID, pID
     having count(*) = 1) t2 on 
 t2.uID = t.uID
 and t2.aID = t.aID
 and t2.cID = t.cID
 and t2.pID = t.pID
 order by occurs

```

returns a set:

```
uID aID cID pID state   occurs
10  240 5000    3860    1   2018-04-17 11:57:24.727
30  240 7000    5938    1   2018-04-17 11:57:26.753

```