Help constructing a complex query

Transact-SQL
1

I need to pull data from 3 tables where one of the columns is a reference column and is used twice in the return data. So I have table three tables

Student s
id name age

Grades g
studentid class grade

Codes c
studentid cde_variable cde_value

I need SELECT s.id, c.cde.value AS 'teacher' where c.cde_varlable = 'a', c.cde_value AS 'school' where c.cde_variable = 'b' and where s.id= g.studentid AND g.studentid = c.studentid.

The reference column is cde_value, so I need to search it using two different filters in the same query. So I thought about using a union.

StudentID Teacher School
123 Jones ' '
345 Thomas ' '

UNION

StudentID Teacher School
123 ' ' Memorial High
345 ' ' Science Academy

But I get (Below are column but I couldn't draw a table)

StudentID Teacher School
123 Jones ' '
345 Thomas ' '
123 ' ' Memorial High
345 ' ' Science Academy

How can I get this query together?

2

This seems like some type of homework assignment. Have you discussed GROUP BY in your class? Or, more specifically, cross tabs?

3

LOL!!! Not a homework assignment...just a bit rusty on my SQL. This query is going to populate a datalist on a panel. The names and subject have been changed to protect the innocent. It' actually medical. I'm applying insurance rules to specific benefits. If the patient is seen by a GYN, the GYN shouldn't be billing for brain surgery. The problem is the reference column c.cde_value above is holding both Place of Service and well as Provider Speciality. So if c.cde_variable is POS the cde_value is the actual location. If c.cde_variable is SPEC then cde_value is the providers speciality. I just need to get it into one query to populate the datatable to display in the datalist. Thanks.

4

Cross tab queries are great for that:

SELECT c.studentid, s.name, c.teacher, c.school
FROM (
    SELECT 
       studentid,
       MAX(CASE WHEN cde_variable = 'a' THEN cde_value END) AS teacher,
       MAX(CASE WHEN cde_variable = 'b' THEN cde_value END) AS school
    FROM Codes
    GROUP BY studentid
) AS c
INNER JOIN Student s ON s.id = c.studentid
ORDER BY c.studentid --<or> s.name