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 [Excute]
(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]

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.
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)