How do I combine 2 tables into 1 datagridview?

I have 2 tables. One is a list of categories created with the following code:

CREATE TABLE [dbo].[Groups] (
[groupname] NVARCHAR (50) NULL,
[Id] INT IDENTITY (1, 1) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

and the other is for storing various data and was created with this:

CREATE TABLE [dbo].[Passwords] (
[title] NVARCHAR (100) NULL,
[username] NVARCHAR (25) NULL,
[password] NVARCHAR (25) NULL,
[url] NVARCHAR (150) NULL,
[notes] NVARCHAR (MAX) NULL,
[group_no] INT NULL,
[Id] INT IDENTITY (1, 1) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

Now in table #2 it is possible to have more than one of the same group_no. Also table1.Id = table2.group_no. What I'm looking to do is display a list (maybe a datagridview) that displays the name of the group (table1.groupnane) and the total number of records in table 2 related to that group (table1.Id = table2.group_no).

I think there's some kind of SQL statement but I just can't place it. I'm using c#.

Is there anyone out there who can help? Thanks.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amira Bedhiafi5y ago
1. SELECT G.[groupname], COUNT(P.Id) AS CountID
2. FROM [dbo].[Groups] AS G
3. INNER JOIN [dbo].[Passwords] AS P ON G.Id = P.group_no
4. GROUP BY G.[groupname]
*db<>fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3b5f523354c4f457e628e2102ce132a1)*