Comples Sql query

Database
Enthusiast

Comples Sql query

There us a table having 3 columns Item_id, Manager_id, Seq_No

We need to find out all items having same manager at seq_no=1 but different manager at other seq_no. There is a possibility where one Item have 2 seq. But another have one seq.

This need to be written in a single sql statement.

Thanks in advance.
Pawan
1 REPLY
Enthusiast

Re: Comples Sql query

Select
TwoSeq1.Item_Id
, TwoSeq1.Manager_Id As Seq1_Mgr
, DiffSeq.Manager_Id As NxtSeq_Mgr
From
(Select Item_Id, Manager_Id
From Tbl
Where Seq_No = 1
Group By 1
Having Count(*) > 1) AS TwoSeq1
Inner Join
(Select Item_Id, Manager_Id
From Tbl
Where Seq_No <> 1
Group by 1,2) As DiffSeq
On TwoSeq1.Item_Id = DiffSeq.Item_Id
And TwoSeq1.Manager_Id <> DiffSeq.Manager_Id
;