How to create a select in the database that runs two tables with a single primary key?

Question

I’m new to programming and I’m developing a note system. The case is as follows: There is a form that registers the notes, where it uses 2 tables of mysql, the notes table (where the code of the note is inserted, the code of the employee, the code of the client, date of issue, value, among others...), the data goes to the textbox. The other table is the notaproducts (where the product data are inserted, such as notaID, productID, Product value, Quantity), which are listed by a listview

There’s another form that I created to search for the client’s notes, so when searching for the client, it lists all the client’s notes. When double-clicking on the listview, opens the form of registering notes, but with all the data of the note open.

The question is there, how will I make a select in the database that runs the two tables opening the notaID? Until the moment that’s what I did:

public List<EntidadeViewPesquisaNota> 
ListarEntidadeViewPesquisaNotaVisu(int codNota)
    {
        var ListarEntidadesNotaVisu = new List<EntidadeViewPesquisaNota>();
        using (MySqlConnection conexao = ConexaoBD.getInstancia().getConexao())
        {
            try
            {
                conexao.Open();
                MySqlCommand comando = new MySqlCommand();
                comando = conexao.CreateCommand();


                comando.CommandText = @"SELECT NotaID, ClienteID, FuncionarioID, NumeroNota, DataEmissao, Status, DataPagamentoFinal, Desconto, ValorPago
                                 FROM nota";
                comando.Parameters.AddWithValue("NotaID", codNota);
                MySqlDataReader reader = comando.ExecuteReader();
                while (reader.Read())
                {
                    //Objeto Usuario
                    var oNota = new EntidadeViewPesquisaNota();
                    oNota.NotaID = Convert.ToInt32(reader["NotaID"].ToString());
                    oNota.FuncionarioID = Convert.ToInt32(reader["FuncionarioID"].ToString());
                    oNota.ClienteID = Convert.ToInt32(reader["ClienteID"].ToString());
                    oNota.NumeroNota = reader["NumeroNota"].ToString();
                    oNota.DataEmissao = Convert.ToDateTime(reader["DataEmissao"].ToString());
                    oNota.Status = (Status)Convert.ToInt16(reader["Status"]);
                    oNota.DataPagamentoFinal = Convert.ToDateTime(reader["DataEmissao"].ToString());
                    if ((reader["ValorPago"] != DBNull.Value))
                        oNota.ValorPago = Convert.ToDecimal(reader["ValorPago"].ToString());
                    if ((reader["Desconto"] != DBNull.Value))
                        oNota.Desconto = Convert.ToDecimal(reader["Desconto"].ToString());
                    oNota.Produtos = new NotaProdutoBD().BuscarProdutosNota(oNota.NotaID);



                    ListarEntidadesNotaVisu.Add(oNota);
                }
            }
            catch (MySqlException ex)
            {
                throw new System.Exception(ex.ToString());
            }
            finally
            {
                conexao.Close();
            }
        }
        return ListarEntidadesNotaVisu;
    }`

And I called him that way:

        private void Bt_Abrir_Click(object sender, EventArgs e)
    {
        var lista = new NotasNG().ListarEntidadeViewPesquisaNotaVisu(Convert.ToInt32(txt_CodigoNota));
        //Verifica se a lista está vazia
        if (lista.Count < 1)
        {
            MessageBox.Show("Sem dados para serem exibidos!", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
            return;
        }

        //Passa a lista para o formulário genérico de pesquisa de funcionário;

        var frmCadNota = new FrmCadNotas();
        frmCadNota.lista = lista;
        frmCadNota.ShowDialog();

        var iRetorno = frmCadNota.iRetorno;
        //iRetorno = 0
        if (iRetorno < 1)
            return;

        txt_CodigoNota.Text = iRetorno.ToString();

        Txt_CodigoNota_Validating(txt_CodigoNota, new CancelEventArgs());
    }

Answer 1

I don’t know the language C#, but from what I understand you have a table Note, which has as primary key Notaid and receives as foreign key Clienteid, and you have another table Notaproduct, which has as foreign key Notaid and Produtoid.

In this case to make a select by receiving the Notaid to list all the associated Notaproduct we would do it:

SELECT Nota.NotaID, Nota.ClienteID, Nota.FuncionarioID, Nota.NumeroNota, Nota.Desconto, Nota.ValorPago, NotaProduto.ProdutoID,NotaProduto.ValorProduto,NotaProduto.Quantidade 
                             FROM Nota, NotaProduto WHERE Nota.NotaID = NotaProduto.NotaID AND Nota.NotaID = id_Nota_que_quer_buscar