As
Eric Lynch[
^] already mentioned, you have to join tables and group by names.
SELECT b.Name, COUNT(b.Name) AS Count
FROM TableA AS a
JOIN TableB AS b ON a.Name = b.Name
GROUP BY b.Name
DataTable dtA = new DataTable();
dtA.Columns.Add(new DataColumn("name", typeof(string)));
dtA.Rows.Add(new object[]{"John"});
dtA.Rows.Add(new object[]{"Joon"});
dtA.Rows.Add(new object[]{"Jorge"});
dtA.Rows.Add(new object[]{"Elizabet"});
dtA.Rows.Add(new object[]{"Suzan"});
DataTable dtB = new DataTable();
dtB.Columns.Add(new DataColumn("name", typeof(string)));
dtB.Rows.Add(new object[]{"John"});
dtB.Rows.Add(new object[]{"Jorge"});
dtB.Rows.Add(new object[]{"Joon"});
dtB.Rows.Add(new object[]{"Elizabet"});
dtB.Rows.Add(new object[]{"Suzan"});
dtB.Rows.Add(new object[]{"Elizabet"});
dtB.Rows.Add(new object[]{"Joon"});
dtB.Rows.Add(new object[]{"Suzan"});
dtB.Rows.Add(new object[]{"John"});
dtB.Rows.Add(new object[]{"Elizabet"});
var result = from a in dtA.AsEnumerable()
join b in dtB.AsEnumerable() on a.Field<string>("name") equals b.Field<string>("name")
group b by b.Field<string>("name") into names
select new
{
Name = names.Key,
Count = names.Count()
};
var result1 = dtA.AsEnumerable()
.Join(dtB.AsEnumerable(),
a => a.Field<string>("name"),
b => b.Field<string>("name"),
(a, b) => new {a, b})
.GroupBy(x=>x.b.Field<string>("name"))
.Select(grp=> new
{
Name = grp.Key,
Count = grp.Count()
});
Note: there's few ways to join tables and therefore a result might be different. For details, please see:
Visual Representation of SQL Joins[
^]
At this moment i've used
INNER JOIN
, due to your sample data and expected result.