CODEWITHSUNDEEP
c#sql.netwpf
zaimoff
zaimoff

Reputation: 69

Select more than one tables in a query - SQL

I have 3 tables that I need to select 2 columns from each. The tables are called EmployeeRank1, EmployeeRank2 and EmployeeRank3. I only know how to select name and pass property from one of the tables, which is EmployeeRank1. However, I need to select name and pass from all the three tables and join them to a single query. Do you have any suggestions? This is the code I use to select name and pass from table EmployeeRank1:

string query = "select * from EmployeeRank1 where Name = @name AND Password = @pass";

The idea behind is that if an entry for name and pass matches the one stored in the tables, a seperate window in WPF is opened. This is the code I have:

private void EmployeeRank1Button_Click(object sender, RoutedEventArgs e)
    {
        try
        {
            string connectionString = ConfigurationManager.ConnectionStrings["CompanyManagementSystemm.Properties.Settings.ZaimovDBConnectionString"].ConnectionString;

            // create a query and select just the record we need 
            string query = "select * from EmployeeRank1 where Name = @name AND Password = @pass";

            // A local sqlconnection in a using statement ensure proper disposal at the end of this code 
            SqlConnection con = new SqlConnection(connectionString);
            con.Open();

            // Let the database do the work to search for the password and name pair
            SqlCommand cmd = new SqlCommand(query, con);
            cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = tbName.Text;
            cmd.Parameters.Add("@pass", SqlDbType.NVarChar).Value = pbPassword.Password;
            SqlDataReader reader = cmd.ExecuteReader();

            // If the reader has rows then the user/pass exists in the db table
            if (reader.HasRows)
            {
                EmployeeRank1 employeeRank1 = new EmployeeRank1();
                employeeRank1.Show();
            }
            
        }
        catch (Exception exception)
        {
            MessageBox.Show(exception.ToString());
        }

Upvotes: 0

Views: 155

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Do you simply want union/union all?

select name, pass from employeerank1
union 
select name, pass from employeerank2
union
select name, pass from employeerank3;

union incurs overhead for removing duplicates. If there are no duplicates or you do not want them removed, use union all.

If you want to look for a name/pass across all three tables, you can use:

select *
from (select name, pass from employeerank1
      union all
      select name, pass from employeerank2
      union all
      select name, pass from employeerank3
     ) er
where er.name = @name and er.pass = @pass;

Upvotes: 1

Related Questions