April 18, 2008 at 1:55 pm
I have 3 tables, STU, PRG, and GAT.
PRG AND GAT are connected through the STU.SN.
I wanted to view all students with a PRG.CD of 127 and SOMEHOW copying date fields from PGM into new table GAT. The date fields are named the same in both tables.
I have written a code below but it only references 2 tables. I don't know how to add a 3rd table. (GAT) I'd appreciate anyone's help. Thanks.
STU.SC = School
STU.SN= Student Number
STU.LN = Last Name
STU.FN = First Name
STU.GR = Grade
PRG.EED = Eligibility Enter Date
PRG.ESD = Eligibility Start Date
PRG.PSD = Program Start Date
PRG.PED = Program End Date
PRG.CO = Comments
GAT.EED = Eligibility Enter Date
GAT.ESD = Eligibility Start Date
GAT.PSD = Program Start Date
GAT.PED = Program End Date
GAT.CO = Comments
Select STU.SC, STU.SN, STU.LN, STU.FN, STU.GR, PRG.CD, PRG.EED, PRG.ESD, PRG.PSD, PRG.PED, PRG.CO
FROM STU
RIGHT JOIN PRG
ON STU.SN = PRG.SN
WHERE PRG.CD = '127'
April 18, 2008 at 3:28 pm
From your description, I am not clear on whether you are trying to update the 3rd table or simply add it to the JOIN. Can you clarify this for us?
Even better, it would help if you could show us some sample data and a sample of the results you are after.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 18, 2008 at 4:14 pm
Sorry for not being clear. Yes this is a 2 part question but I was focusing on the first part.
1. Develop the query to join 3 tables if PRG.CD = 127. STU.SN is the primary key.
A sample of the data i would like is below. As i had mentioned before, the GAT table is empty now so there are no dates.
STU.SC STU.SN PRG.CD PRG.ESD PRG.EED PRG.PSD PRG.PED GAT.ESD GAT.EED GAT.PSD
115402 127 12/9/200412/9/2004 12/9/2004 12/9/2004
1154571271/11/20071/11/2007 1/11/20071/11/2007
1155461272/28/20082/28/20082/28/20082/28/2008
1155951271/16/20081/16/20081/16/20081/16/2008
2. Once i am able to view the 3 tables, i would want to copy the dates from the PRG fields to the GAT fields. The query from step 1 would help after this process to look for missing data not copied.
Hope this clarifies more.
April 18, 2008 at 4:27 pm
What you need is another outer join. Check out this example and see if you can get your UPDATE statement from it:
DECLARE @STU TABLE (SC int, SN int)
DECLARE @PRG TABLE (SN int, CD int, ESD datetime, EED datetime, PSD datetime, PED datetime)
DECLARE @GAT TABLE (SN int, ESD datetime, EED datetime, PSD datetime, PED datetime)
INSERT INTO @STU
SELECT 115, 402 UNION ALL
SELECT 115, 457 UNION ALL
SELECT 115, 546 UNION ALL
SELECT 115, 595
INSERT INTO @PRG
SELECT 402, 127, '12/9/2004', '12/9/2004', '12/9/2004', '12/9/2004' UNION ALL
SELECT 457, 127, '1/11/2007', '1/11/2007', '1/11/2007', '1/11/2007' UNION ALL
SELECT 546, 127, '2/28/2008', '2/28/2008', '2/28/2008', '2/28/2008' UNION ALL
SELECT 595, 127, '1/16/2008', '1/16/2008', '1/16/2008', '1/16/2008'
SELECT STU.SC, STU.SN, PRG.CD, PRG.ESD, PRG.EED, PRG.PSD, PRG.PED, GAT.ESD, GAT.EED, GAT.PSD, GAT.PED
FROM @STU STU
LEFT JOIN @PRG PRG
ON STU.SN = PRG.SN
LEFT JOIN @GAT GAT
ON GAT.SN = STU.SN
WHERE PRG.CD = '127'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply