CODEWITHSUNDEEP
c#sql
Dim
Dim

Reputation: 122

Select from multiple tables?

enter image description hereI have two tables one table's name is (memberform) and it has columns id,name,mobile example 1,dimitris,69xxxxxxx, and a second table (groups) with columns name,groupname,memberid example dimitris,dancegroup,1 (memberid is the same with id) I want to extract into a richtextbox where groupname from groups = combobox1 and where memberid from row which groupname exists is same with memberform.id

i'm trying something like this

using (var command = new SqlCommand("select mobile from memberform where memberform.id=groups.memberid and groups.groupname='" + comboBox1.Text + "'", con)) // 
using (var reader = command.ExecuteReader())

Upvotes: 0

Views: 116

Answers (2)

Thadeu Fernandes
Thadeu Fernandes

Reputation: 505

Why not select from the first table, get the number then use it to select from the second table?

@Edit:

private void GetData()
{
    // Get the ID using the name
    string id, yourData;
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("SELECT * FROM Table1 WHERE name=@name", con);
    cmd.Parameters.Add("@name", "dimitris");
    con.Open();
    SqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
        id = reader["Id"].ToString();
    con.Close();

    // Get whatever you want using that ID
    cmd.CommandText = "SELECT * FROM Table2 WHERE Id=@id";
    cmd.Parameters.Add("@id", id);
    con.Open();
    reader = cmd.ExecuteReader();
    while (reader.Read())
        yourData = reader["ColumnName"].ToString();
    con.Close();
}

Upvotes: 0

Arulkumar
Arulkumar

Reputation: 13247

The raw SQL query is 

SELECT M.mobile 
FROM memberform M 
JOIN groups G ON G.memberid = M.id 
WHERE G.groupname = 'dancegroup'

the same can be written in your sqlcommand is 

using (var command = new SqlCommand("SELECT M.mobile FROM memberform M JOIN groups G ON G.memberid = M.id WHERE G.groupname = '" + comboBox1.Text + "'", con))

UPDATE:

The above approach can be possible for SQL injection attack, so explicitly pass the parameter by SqlParameter

using (var command = new SqlCommand("SELECT M.mobile FROM memberform M JOIN groups G ON G.memberid = M.id WHERE G.groupname = @GroupName", con))
{
    command.Parameters.Add(new SqlParameter("GroupName", comboBox1.Text);
    using (var reader = command.ExecuteReader())
    ....

Upvotes: 1

Related Questions