CODEWITHSUNDEEP
c#sql
avocadoLambda
avocadoLambda

Reputation: 1046

C# code to check one columns data from table has exist in other tables

I want to check one record from table A to check if it exists in table B. I calculate how much data has been entered in table B.

Example:

   A                       B
------------------------------
John                     John
Joon                     Jorge
Jorge                    Joon
Elizabet                 Elizabet
Suzan                    Suzan
                         Elizabet
                         Joon
                         Suzan
                         John
                         Elizabet

The answer should be like this:

John = 2
Joon = 2
jorge = 1
Elizabet = 3
Suzan = 2

What I have tried:

using (ImportInvoiceMasterForm.ApplicationSqlConnection2 = new SqlConnection(MainForm.ApplicationDataBase))
            {
                ImportInvoiceMasterForm.ApplicationSqlConnection2.Open();
                using (ImportInvoiceMasterForm.ApplicationSqlCommand1 = new SqlCommand("SELECT * FROM TBL_Stock_Item", ImportInvoiceMasterForm.ApplicationSqlConnection2))
                using (ImportInvoiceMasterForm.ApplicationSqlDataReader1 = ImportInvoiceMasterForm.ApplicationSqlCommand1.ExecuteReader())
                {
                    while (ImportInvoiceMasterForm.ApplicationSqlDataReader1.Read())
                    {
                        ImportInvoiceMasterForm.GetImportQuantity = 0;
                        #region Get Import Quantity
                        using (ImportInvoiceMasterForm.ApplicationSqlConnection1 = new SqlConnection(MainForm.ApplicationDataBase))
                        {
                            ImportInvoiceMasterForm.ApplicationSqlConnection1.Open();
                            using (ImportInvoiceMasterForm.ApplicationSqlCommand = new SqlCommand("SELECT * FROM TBL_Import_Items WHERE ImportItemName='" + ImportInvoiceMasterForm.ApplicationSqlDataReader1.GetString(1) + "'", ImportInvoiceMasterForm.ApplicationSqlConnection1))
                            using (ImportInvoiceMasterForm.ApplicationSqlDataReader = ImportInvoiceMasterForm.ApplicationSqlCommand.ExecuteReader())
                            {
                                while (ImportInvoiceMasterForm.ApplicationSqlDataReader.Read())
                                {
                                    GetImportQuantity += double.Parse(ImportInvoiceMasterForm.ApplicationSqlDataReader.GetValue(4).ToString());
                                }
                            }
                        }
                        #endregion Get Import Quantity
                    }
                }
            }

Upvotes: 1

Views: 343

Answers (2)

Raj
Raj

Reputation: 19

Select A.name, Count(B.name) As Num
FROM A join B 
Where B.name=A.name
Group By A.name

This would give you the solution you want

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

This looks like an aggregation. One method is join/group by:

select a.name, count(b.name)
from a left join
     b
     on a.name = b.name
group by a.name;

The left join allows counts of 0.

If names can be repeated in a, then a correlated subquery is a better solution (assuming you want to preserve the duplicates in a):

select a.*, (select count(*) from b where b.name = a.name) as cnt
from a;

Upvotes: 2

Related Questions