How can I list the students in the CIS department? (SQL Server Query)

I need to write the SQL script for the above question.

Student table contains students' IDs, names, Addresses.

MajorMinor table contains 4 departments with unique code.
Name is a field and CIS is an attribute. Code(primary key) is a field and CIS is 13.

Student declares major. Declares table has StudentID and MajorMinorCode. The only student, his StudentID is 3579, has MajorMinorCode 13. (Meaning he is the only student who declared CIS as his major.)

I created and populated my database in SQL Server Management.

I am having trouble if I am selecting 2 tables or just one table with any other clauses?
Any help is appreciated! Thanks!That looks like an assignment / home work .

Can you kindly post what you have tried to solve the mentioned problem.|||

Quote:

Originally Posted by debasisdas

That looks like an assignment / home work .

Can you kindly post what you have tried to solve the mentioned problem.

Here you go.
SELECT Student.Name
FROM (Student INNER JOIN Declares ON Student.ID = Declares.StudentID) INNER JOIN MajorMinor ON Declares.MajorMinorCode = MajorMinor.Code
WHERE ((MajorMinor.Name)="CIS"));

How can I find the matching table from a group of tables? (difficult to explain

Let's say I have a list of IDs called EntryID and each EntryID can belong to ONE table out of a group of six, what is the best way to get a listing of these?

For example:

select r.*
from #Reminders r
left join mytable1 mt1 on (r.EntryID = mt1.EntryID)
left join mytable2 mt2 on (r.EntryID = mt2.EntryID)
left join mytable3 mt3 on (r.EntryID = mt3.EntryID)
left join mytable4 mt4 on (r.EntryID = mt4.EntryID)

As you can see, #Reminders has one field called EntryID (and many rows).

In my example above, only ONE of those tables will actually be able to join but I have no idea which one has the matching EntryID.

What is the best way for me to do this? I want to grab "ReportStatus" from the corresponding "mytable"... (each "mytable" has a ReportStatus column)How come these are distributed between 6 tables?|||Because each table contains different information in addition to the common EntryID field...|||Ok. I will take your word that it is normalised properly.

How many rows in #Reminders? Is there anyway of knowing which of the tables an id belongs to without actually looking in the tables (for example I worked somewhere where they split off the table into 8 tables based on the mod 8 +1 value of each ID)?|||Actually - if there are 6 tables all with the same ID for different entities, do you not have a master table for common attributes which holds all the PKs? How do you enforce the requirement that an id can only appear in one of the six tables?|||Ok. I will take your word that it is normalised properly.

Daaaaaangerous!