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?