C#•2y 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; }
    }

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" after that I use: SELECT OutfitId WHERE StudentId = @id After that I run 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 ?

3 Replies

Jimmacle•2y ago

yes, the term you're looking for is a JOIN there's a brief explanation/example here https://www.w3schools.com/sql/sql_join.asp

Tvde1•2y ago

Have you already heard about the JOIN word in SQL? It'll be very useful to you :) Note that if you will never have students sharing the same outfit, you can make StudentId a column on your outfit table. It'll remove a step

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ Getting SQL data from a relationship table