C#•3y ago

❔ Getting SQL data from a relationship table

Hello, for my school assignment I have to make a winforms app. Currently I have 3 tables in my SQL Database and 2 models in my code. The tables are shown in the picture and my Student class looks like this:

    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int StudentNumber { get; set; }
        public string Password { get; set; }
        public List<Outfit> Outfits { get; set; }
        public int XP { get; set; }
        public int Level { get; set; }
    }

    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int StudentNumber { get; set; }
        public string Password { get; set; }
        public List<Outfit> Outfits { get; set; }
        public int XP { get; set; }
        public int Level { get; set; }
    }

    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int StudentNumber { get; set; }
        public string Password { get; set; }
        public List<Outfit> Outfits { get; set; }
        public int XP { get; set; }
        public int Level { get; set; }
    }

    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int StudentNumber { get; set; }
        public string Password { get; set; }
        public List<Outfit> Outfits { get; set; }
        public int XP { get; set; }
        public int Level { get; set; }
    }

Since the student class has a list of outfits I made a relationship table in my SQL database but I have no idea on how to retrieve this in the most efficient way possible.

Currently I run the following queries:

"SELECT Id, Name, StudentNumber, Password, XP FROM Students"

"SELECT Id, Name, StudentNumber, Password, XP FROM Students"

"SELECT Id, Name, StudentNumber, Password, XP FROM Students"

"SELECT Id, Name, StudentNumber, Password, XP FROM Students"

after that I use:

SELECT OutfitId WHERE StudentId = @id

SELECT OutfitId WHERE StudentId = @id

SELECT OutfitId WHERE StudentId = @id

SELECT OutfitId WHERE StudentId = @id

After that I run

SELECT Id, Name, Description, ImageURL, Cost WHERE Id = @id

SELECT Id, Name, Description, ImageURL, Cost WHERE Id = @id

SELECT Id, Name, Description, ImageURL, Cost WHERE Id = @id

SELECT Id, Name, Description, ImageURL, Cost WHERE Id = @id

It feels like I'm doing unnecessary steps, is it possible to do all of this in one query ?

❔ Getting SQL data from a relationship table

Similar Threads

Similar Threads

Similar Threads