In this code, we will get ONLY the records with content. If their count is 0, we will not return any records.
SQL Server Database Script
To use this script.
In SSMS, [Right-click] on your [Server Name] and choose [New Query]
Copy and paste this code and choose [Execute]

(OR)
If you have a Temp Database already created, then change the name from
[cffDB] to [Your-DB-Name]
Then Right-click on your Temp Database and choose [New Query]
Copy and paste this code, and click [Execute]
[SQL Server - Return only records where the count is not 0]
CFFCS | CarrzSynEdit: | SQL Script 
USE [cffDB]
GO
/****** Object:  Table [dbo].[Cats]    Script Date: 3/23/2024 3:47:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cats](
	[CatID][int] IDENTITY(1,1) NOT NULL,
	[CatName][nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Cats] PRIMARY KEY CLUSTERED 
(
	[CatID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Contents]    Script Date: 3/23/2024 3:47:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Contents](
	[ConID][int] IDENTITY(1,1) NOT NULL,
	[SCID][int] NOT NULL,
	[ConName][nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Content] PRIMARY KEY CLUSTERED 
(
	[ConID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[SubCat]    Script Date: 3/23/2024 3:47:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SubCat](
	[SCID][int] IDENTITY(1,1) NOT NULL,
	[CatID][int] NOT NULL,
	[SubName][nvarchar](50) NOT NULL,
 CONSTRAINT [PK_SubCat] PRIMARY KEY CLUSTERED 
(
	[SCID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Cats] ON 
GO
INSERT [dbo].[Cats] ([CatID], [CatName]) VALUES (1, N'Rock')

GO
INSERT [dbo].[Cats] ([CatID], [CatName]) VALUES (2, N'Country')

GO
INSERT [dbo].[Cats] ([CatID], [CatName]) VALUES (3, N'Blues')

GO
SET IDENTITY_INSERT [dbo].[Cats] OFF
GO
SET IDENTITY_INSERT [dbo].[Contents] ON 
GO
INSERT [dbo].[Contents] ([ConID], [SCID], [ConName]) VALUES (1, 1, N'KISS')

GO
INSERT [dbo].[Contents] ([ConID], [SCID], [ConName]) VALUES (2, 1, N'W.A.S.P.')

GO
INSERT [dbo].[Contents] ([ConID], [SCID], [ConName]) VALUES (3, 1, N'Bobaflex')

GO
INSERT [dbo].[Contents] ([ConID], [SCID], [ConName]) VALUES (4, 2, N'Hank Williams Jr.')

GO
INSERT [dbo].[Contents] ([ConID], [SCID], [ConName]) VALUES (5, 2, N'Alabama')

GO
INSERT [dbo].[Contents] ([ConID], [SCID], [ConName]) VALUES (6, 3, N'BB King')

GO
SET IDENTITY_INSERT [dbo].[Contents] OFF
GO
SET IDENTITY_INSERT [dbo].[SubCat] ON 
GO
INSERT [dbo].[SubCat] ([SCID], [CatID], [SubName]) VALUES (1, 1, N'Hard Rock')

GO
INSERT [dbo].[SubCat] ([SCID], [CatID], [SubName]) VALUES (2, 1, N'Southern Rock')

GO
INSERT [dbo].[SubCat] ([SCID], [CatID], [SubName]) VALUES (3, 2, N'Blue Grass')

GO
INSERT [dbo].[SubCat] ([SCID], [CatID], [SubName]) VALUES (5, 3, N'R&B')

GO
SET IDENTITY_INSERT [dbo].[SubCat] OFF
GO


SQL Query for returning only records with a count other than 0.
[SQL Server - Return only records where the count other than 0]
CFFCS | CarrzSynEdit: | SQL Script 

SELECT   (SELECT COUNT(SCID) AS ctSubs FROM Contents where SCID = SubCat.SCID) as ctSubs 
Cats.CatID, SubCat.SubName FROM Cats INNER JOIN
SubCat ON Cats.CatID = SubCat.CatID
WHERE   (Cats.CatID = 1) and not (SELECT COUNT(SCID) AS ctSubs FROM Contents where SCID = SubCat.SCID) = 0
-- Test with 

-- Cats.CatID = 1 (Will return records)

-- Cats.CatID = 2 (Will return records)

-- Cats.CatID = 3 (Will NOT return records)