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

T.Zacks 3,986 Reputation points
2021-02-19T12:25:26.117+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,575 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 113.7K Reputation points
    2021-02-19T14:05:43.873+00:00

    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
    
    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points
    2021-02-19T14:31:12.827+00:00

    Try this:

    SELECT t1.*
    FROM [dbo].[tblData] AS t1
    WHERE EXISTS (
        SELECT 1 FROM 
        [dbo].[tblData] 
        WHERE [LineItem] = t1.[LineItem] AND [EarningsID] <> t1.[EarningsID]
    );
    
    1 person found this answer helpful.
    0 comments No comments