Query with related tables in SQL and C #

Question

Query with related tables in SQL and C #

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have this form:

This form feeds two tables in the database projects and students , all right up to that point, since records are kept, both new projects, and students by separated. What I do not have very clear is in assigning students to a project and keep it or relate the data and when a query is made, for example of projects, throw what students are assigned to him same, vice versa, when consulting a student throw to what project is associated.

I have knowledge about the Primary Key and the Foreign Keys , I just need a help / guide in creating the above mentioned relationship.

This is the code I use in my current query (student class), how do I modify it to make use of the "INNER JOIN" and that the query give me data from the two tables?

public static List<Estudiantes> BuscarEstudiante(String pnombre, String papellido)
    {
        List<Estudiantes> lista = new List<Estudiantes>();
        using (SqlCeConnection conexion = BDc.ObtnerCOnexion())
        {
            SqlCeCommand comando = new SqlCeCommand(string.Format("SELECT idEstudiantes, cedula, nombre, apellido, carrera, condicion, codProyecto FROM estudiantes WHERE nombre LIKE '%{0}%' and apellido LIKE '%{1}%'", pnombre, papellido), conexion);

            SqlCeDataReader reader = comando.ExecuteReader();

            while (reader.Read())
            {
                Estudiantes pestudiante = new Estudiantes();
                pestudiante.idEstudiantes = reader.GetInt32(0);
                pestudiante.cedula = reader.GetString(1);
                pestudiante.nombre = reader.GetString(2);
                pestudiante.apellido = reader.GetString(3);
                pestudiante.carrera = reader.GetString(4);
                pestudiante.condicion = reader.GetString(5);
                pestudiante.codProyecto = reader.GetInt32(6);

                lista.Add(pestudiante);
            }
            conexion.Close();
            return lista;
        }
    }

sql c# sql-server

asked by Jose Iglesias 11.03.2017 в 23:23

source

2 answers

For loop in python Extract and save data from several related tables in Laravel

score 1 · Answer 1

For what I understand, you want to know how to make the project student relationship.

You can join the two tables by means of a tea.

example:

by means of a student you can look for the projects assigned to them. and through a project you can get the students assigned.

Update.

I would create the middle table above Student_Project.

the query in that table would be.

select e.name, e.last name from Proyecto_Estudiante as pe inner join project as p on p.id = pe.proyecto_id inner join student as e on e.id = pe.student_id where e.name like '% name%'

and if you want to use only those 2 tables, in one of them you have to be referencing the other and put the join. example.

SELECT studentIDs, cedula, name, surname, career, condition, codProject FROM students as e inner join Project as p on p.id = e.idproject WHERE name LIKE '% {0}%' and last name LIKE '% {1}%'

score 0 · Answer 2

SELECT t1.NombreProyecto,t2.NombreEstudiante 
FROM tablaProyecto t1, tablaAlumno t2 
WHERE t1.Id = t2.Id 
ORDER BY  t1.NombreProyecto asc

With this query you fill a datatable and this is assigned to the datasource of the datagridview