❔ 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; }
    }

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"

after that I use:

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

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

    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; }
    }

"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

After that I run

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

❔ Getting SQL data from a relationship table

Similar Threads

Similar Threads

Similar Threads