-1

I'm doing a project on .net (visual studio) in which i'm using sql database. I want to know how can I compare between many values at the same time. Is there an easy & simple way to do it?

The compare between two tables. For example:

Table 1 (student) has:
Name
Id
Book1 (art for example)
Book2 (math)
Book3 (math 2)
Book4 (history)
Book5 (physics)

Table 2 (teacher) has:
Name
Id
Book1
Book2
Book3
Book4
Book5

For each student, I need to compare his books (name of the book itself) by the teacher's books (to see if there is matching). Also, is there a more efficient to do my database?

Any suggestions?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
FNM
  • 7
  • 6

2 Answers2

0

There are several ways to do your project. If it is easy or not depends on your need, constraints, your capacity etc.

As far as I understand, you should have 4 tables for a better db design. You will have; (in fact; you should have more tables for better design see What is Normalisation (or Normalization)?)

  • Table 1: Students (Id,Name)
  • Table 2: Teachers (Id,Name)
  • Table 3: StudentBooks(StudentId,NameOfTheBook)
  • Table 4: TeacherBooks(TeacherId,NameOfTheBook)

Then you can execute SQL statement like;

-- Fetch name of the books for a specific student
SELECT b.NameOfTheBook 
FROM Students AS s INNER JOIN StudentBooks AS b ON s.Id = b.StudentId
WHERE s.Id = <id of the student>

-- Fetch name of the books for a specific teacher
SELECT b.NameOfTheBook 
FROM Teachers AS t INNER JOIN TeacherBooks AS b ON t.Id = b.TeacherId
WHERE t.Id = <id of the student>

This is just an example, so you can enhance your model and execute more complex SQL statements.

CL.
  • 173,858
  • 17
  • 217
  • 259
ali
  • 1,301
  • 10
  • 12
0

The earlier answers cover the advantages of further normalizing your database structure, though I would suggest also adding a Books(Id, Name) and having the StudentBooks and TeachersBooks tables relate to the Id of the Books table. This way you are able to account for renaming books (i.e. fixing mispellings). That should result in more consistent data in the long run as well.

VortexSuit
  • 56
  • 4