SQL select multiple variables

Question

I have 3 tables, Subject, SubjectTeacher and Head. A business rule is each Subject has one Head.

Subject
ID         Name            Code  
1          Mathematics     Math  
2          Biology         Bio  
3          Zoology         Zoo  

SubjectTeacher
TeacherID         JoinDate           SubjectID
1                 2001-12-11         1
2                 2004-12-11         2
3                 2002-12-11         3

Head
TeacherID         StartDate          EndDate
1                 2001-12-11         2016-12-11
2                 2004-12-11         2014-12-11
3                 2002-12-11         2017-12-11

I have 2 variables, MathHead and BioHead
I would like to write a query that populates these 2 variables
There are 2 ways I identified. One is temporary tables. Too heavy for a seemingly simple problem.
Other is multiple queries, one for each variable. This is simple/easy but as the variables increase, the queries increase too.
Is there a better way?
Update: I am doing something like this. Also added a link table(SubjectTeacher) above

SELECT @BioHead = TeacherID
FROM Head head
JOIN SubjectTeacher st on st.TeacherID = head.TeacherID
JOIN Subject subject on st.SubjectID = subject.ID
WHERE subject.Name = 'Biology'

You mention you have variables, which I am assuming are T-SQL variables. To make it easier please show the query you have already and/or relevant code to get to the information you want. — Jake H, Jan 22 '14 at 03:10
possible dup of this? http://stackoverflow.com/questions/1340775/sql-select-multi-columns-into-multi-variable — Mark Giaconia, Jan 22 '14 at 03:16
No Mark. I want to apply filters on joining tables to say which variables should hold what data. — Null Head, Jan 22 '14 at 03:26

score 0 · Answer 1 · answered Jan 22 '14 at 03:12

0

A simple way to have this business rule applied in the database would be to just simply have a column on subject with foreign key restraint to teacher, unless that is HeadTeacherId and not a table name. Cant really tell with it formatted as such.

answered Jan 22 '14 at 03:12

Jake H

1,720
1
12
13

score 0 · Answer 2 · answered Jan 22 '14 at 03:33

0

perhaps you should union the queries and output a resultset like this, sort of like a "key value" approach:

TYPE| VALUE
head | 'The head of the dep'
teacher | 'the teacher's name'
subject| 'Biology'

Please provide your desired output if this doesn't make sense.

answered Jan 22 '14 at 03:33

Mark Giaconia

3,844
5
20
42

I want TeacherID to go into variables – Null Head Jan 22 '14 at 03:45
Will you always know how many TeacherIDs you will return? Seems like you should be returning a resultset if you can't be certain. – Mark Giaconia Jan 22 '14 at 03:54
I know theres only one head per subject. – Null Head Jan 22 '14 at 04:15
so you can have N teacherIDs returned? if so, I think you need to abandon the variables and just return a resultset/dataset with a select. – Mark Giaconia Jan 22 '14 at 14:07
No, I know there is only one head per subject and I know subject count. So I am sure of result count. Hence variables should be fine. BTW, the solutions not biggie. – Null Head Jan 23 '14 at 20:40

SQL select multiple variables

2 Answers2