How to show those data which has two different kind of EarningsID

Question

Here is Schema and data. which help to re-produce scenario at user end.

CREATE TABLE [dbo].[tblData](
 [EarningsID] [int] NULL,
 [LineItem] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

INSERT [dbo].[tblData] ([EarningsID], [LineItem]) VALUES (11, N'LoyaltyOne - Revenue')
GO
INSERT [dbo].[tblData] ([EarningsID], [LineItem]) VALUES (11, N'LoyaltyOne - Revenue')
GO
INSERT [dbo].[tblData] ([EarningsID], [LineItem]) VALUES (11, N'LoyaltyOne - Revenue')
GO
INSERT [dbo].[tblData] ([EarningsID], [LineItem]) VALUES (12, N'LoyaltyOne - Revenue')
GO
INSERT [dbo].[tblData] ([EarningsID], [LineItem]) VALUES (12, N'LoyaltyOne - Revenue')
GO
INSERT [dbo].[tblData] ([EarningsID], [LineItem]) VALUES (12, N'LoyaltyOne - Revenue')
GO
INSERT [dbo].[tblData] ([EarningsID], [LineItem]) VALUES (12, N'Card Services - EBITDA, Net')
GO
INSERT [dbo].[tblData] ([EarningsID], [LineItem]) VALUES (12, N'Card Services - EBITDA, Net')
GO
INSERT [dbo].[tblData] ([EarningsID], [LineItem]) VALUES (12, N'Card Services - EBITDA, Net')
GO
INSERT [dbo].[tblData] ([EarningsID], [LineItem]) VALUES (11, N'Card Services - EBITDA, Net')
GO
INSERT [dbo].[tblData] ([EarningsID], [LineItem]) VALUES (11, N'Card Services - EBITDA, Net')
GO
INSERT [dbo].[tblData] ([EarningsID], [LineItem]) VALUES (11, N'Card Services - EBITDA, Net')
GO
INSERT [dbo].[tblData] ([EarningsID], [LineItem]) VALUES (11, N'Gross Profit')
GO
INSERT [dbo].[tblData] ([EarningsID], [LineItem]) VALUES (11, N'Gross Profit')
GO
INSERT [dbo].[tblData] ([EarningsID], [LineItem]) VALUES (11, N'Gross Profit')
GO

I want to show all line item which has two different kind of EarningsID. see each line item has two different kind of EarningsID and those are 11 and 12
but Gross Profit line item has only one earningid that is 11. so it should not come into select result. i tried this but it did not worked.

select * from tblData 
WHERE LineItem IN
(
Select LineItem from tblData
WHERE EarningsID IN (11,12)
group by LineItem
)
order by EarningsID

my above sql is showing Gross Profit which suppose not to come in result because it has one earningid which is 11.

only those line item should come which has 11 and 12 earningid.

where i am making the mistake in sql. please help.

Answer 1

Try two queries:

select LineItem
from #tblData
where EarningsID in (11, 12)
group by LineItem
having count(distinct EarningsID) >= 2

select LineItem
from #tblData
group by LineItem
having count(distinct EarningsID) >= 2

Answer 2

Try this:

SELECT t1.*
FROM [dbo].[tblData] AS t1
WHERE EXISTS (
    SELECT 1 FROM 
    [dbo].[tblData] 
    WHERE [LineItem] = t1.[LineItem] AND [EarningsID] <> t1.[EarningsID]
);