January 21, 2016 at 7:43 am
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?
January 21, 2016 at 8:27 am
Hey there,
Do you mind having a quick look at this link (http://www.sqlservercentral.com/articles/Best+Practices/61537/)[/url]? It's all about how best to post a question on these forums.
As things stand, it's a little difficult to see what you want to do. But if you can knock up readily consumable sample data, as well as expected results based on that sample data, everything becomes much easier and you'll find that you get a tested answer back that will fulfil your requirements.
Thanks.
January 21, 2016 at 8:31 am
Duplicate thread. Please reply in here: http://www.sqlservercentral.com/Forums/Topic1754550-392-1.aspx
January 21, 2016 at 8:34 am
Also, seems you've asked this question in two forums (T-SQL and General).
I know you're probably hoping that just results in you getting an answer faster, but instead it just fragments efforts to help you. The unpaid volunteers on this site normally go through all of the forums, so eventually they'll come across both of your questions, but it'd be much easier for everyone involved if you just pick one and write "sorry this is a duplicate, see the post here for the actual content).
Thanks.
January 21, 2016 at 9:40 am
I apologize for posting on both forums but after posting on this forum -SQL Server 2008 General, it seemed more fitting a question for the T-SQL forum. On a side note any actual help?
January 21, 2016 at 9:48 am
From the other thread:
Luis Cazares (1/21/2016)
You just need to use 2 joins in your query.There's an example at the bottom of this page: http://www.sql-join.com/
January 21, 2016 at 10:06 am
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Student','U') IS NOT NULL
DROP TABLE #Student
--===== Create the test table with
CREATE TABLE #Student
(
ID INT (10) PRIMARY KEY,
Name Varchar(50),
Age Varchar (50)
)
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Grades','U') IS NOT NULL
DROP TABLE #Grades
--===== Create the test table with
CREATE TABLE #Grades
(
StudentID INT (10) FOREIGN KEY,
Class Varchar(50),
Grade Varchar (50)
)
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Codes','U') IS NOT NULL
DROP TABLE #Codes
--===== Create the test table with
CREATE TABLE #Codes
(
StudentID INT (10) FOREIGN KEY,
Cde_Variable Varchar(50),
Cde_Value Varchar (50)
)
SELECT Student.ID, Codes.Cde_Value AS 'Teacher' (WHERE Codes.Cde_Variable = 'POS'), Codes.Cde_Values AS 'School' (WHERE Codes.Cde_Variable = 'SPEC'
FROM Student
JOIN Grades ON Student.ID = Grades.StudentID
JOIN Codes ON Grades.StudentID = Codes.StudentID
My question is how do I pull cde.Value twice in the same query with to different criteria filters?
If this explanation doesn't make sense please comment so I can try it again!
January 21, 2016 at 10:17 am
It seems that you have an EAV design which can be a problem with queries and you might want to change it to a properly normalized model.
To query this designs, my suggestion is to use something called cross tabs.
Here's an example based on some assumptions from your posted code.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Student','U') IS NOT NULL
DROP TABLE #Student;
--===== Create the test table with
CREATE TABLE #Student
(
ID INT PRIMARY KEY,
Name Varchar(50),
Age Varchar (50)
);
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Grades','U') IS NOT NULL
DROP TABLE #Grades;
--===== Create the test table with
CREATE TABLE #Grades
(
StudentID INT FOREIGN KEY REFERENCES #Student(ID),
Class Varchar(50),
Grade Varchar (50)
);
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Codes','U') IS NOT NULL
DROP TABLE #Codes;
--===== Create the test table with
CREATE TABLE #Codes
(
StudentID INT FOREIGN KEY REFERENCES #Student(ID),
Cde_Variable Varchar(50),
Cde_Value Varchar (50)
);
SELECT s.ID AS StudentID,
MAX( CASE WHEN c.Cde_Variable = 'POS' THEN c.Cde_Value END) AS Teacher,
MAX( CASE WHEN c.Cde_Variable = 'SPEC' THEN c.Cde_Value END) AS School
FROM #Student s
JOIN #Grades g ON s.ID = g.StudentID
JOIN #Codes c ON g.StudentID = c.StudentID
GROUP BY s.ID;
DROP TABLE #Student;
DROP TABLE #Grades;
DROP TABLE #Codes;
January 21, 2016 at 10:20 am
Further Explanation: The problem is that cde_value is a reference column so values in that column can be anything. The cde_variable determines what the cde_value means. For example, if cde_variable is POS the corresponding data in cde_value is the place_of_service. If the cde_variable is SPEC the cde_value means speciality.
January 21, 2016 at 1:08 pm
Now, either you build a query with all possible columns or you build a dynamic query to get the columns available.
I'll suggest that you go for the first option as it seems that you don't have much experience with SQL and it's easy to mess up things and open vulnerabilities when doing it wrong.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply