query

Question

query

graham19209019

Old Hand

Points: 370
More actions
January 25, 2007 at 9:30 pm

#172002

I have been scratching my head trying to get result set I was hoping I could get some help from the forum. The first portion return all students who have a certain code associated with them from two schools. The second part of the query I then need to find all siblings of the first query based upon a phone number and last name from all schools.
I cannot figure out the logic too pull all student from query1 + query2 that are not in query1 but match up on student lastName + phone number from query1, if that makes sense
SELECT DISTINCT StudentDemographic_duck.[_DistBldg], StudentDemographic_duck.[_Year], StudentDemographic_duck.LastName, StudentDemographic_duck.FirstName, StudentEMIS_duck.Disadvantagement, StudentDemographic_duck.Status, StudentDemographic_duck.StudentHomePhone, StudentDemographic_duck.StudentId, StudentDemographic_duck.StudentNumber
FROM StudentDemographic_duck INNER JOIN StudentEMIS_duck ON StudentDemographic_duck.StudentId = StudentEMIS_duck.StudentId
WHERE (((StudentDemographic_duck.[_DistBldg]) In ('E057','E056')) AND ((StudentEMIS_duck.Disadvantagement)='1') AND ((StudentDemographic_duck.Status) Not In ('I')));
SELECT distinct StudentEMIS_duck.Disadvantagement, StudentDemographic_duck.StudentHomePhone, StudentDemographic_duck.StudentNumber, StudentDemographic_duck.LastName, StudentDemographic_duck.FirstName, StudentDemographic_duck.Status
FROM StudentEMIS_duck INNER JOIN StudentDemographic_duck ON StudentEMIS_duck.StudentNumber = StudentDemographic_duck.StudentNumber
where (((StudentDemographic_duck.Status) Not In ('I')));
Thanks for any help
Graham

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Graham

My first advice to you would be to alias your tables and get rid of those unnecessary parentheses. That will make your queries much easier to read.

Now, will this work?

John

WITH

BothSchools AS (

SELECT DISTINCT

d.LastName, d.FirstName, e.Disadvantagement, d.Status, d.StudentHomePhone, d.StudentNumber

FROM StudentDemographic_duck d INNER JOIN StudentEMIS_duck e

ON d.StudentId = e.StudentId

WHERE d.[_DistBldg] In ('E057','E056')

AND e.Disadvantagement = '1'

AND d.Status <> 'I'

)

SELECT

b.Disadvantagement, b.StudentHomePhone, b.StudentNumber, b.LastName, b.FirstName, b.Status

FROM BothSchools b INNER JOIN StudentDemographic_duck d

ON b.LastName = d.LastName

AND b.StudentHomePhone = d.StudentHomePhone

AND b.FirstName <> d.FirstName

UNION ALL

SELECT b.Disadvantagement, b.StudentHomePhone, b.StudentNumber, b.LastName, b.FirstName, b.Status

FROM BothSchools b INNER JOIN StudentEMIS_duck e

ON b.LastName = e.LastName

AND b.StudentHomePhone = e.StudentHomePhone

AND b.FirstName <> e.FirstName

graham19209019 Old Hand Points: 370 More actions · Answer 2

Worked great thanks and for the tip about the aliases. I was doing this in access it will make my queries much easier to read

Thanks

Graham

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Graham

Glad it worked. I'm sure you have thought about what happens if a pair of siblings don't share the same surname, or live at different addresses, or live at the same address but give different phone numbers...

John

graham19209019 Old Hand Points: 370 More actions · Answer 4

John

I have thought about it for sure but I am unsure of how to implement it. How could I could I write the sql if the last name is different but they have the same phone number and address?

Thanks

Graham