Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
collie
Constraint Violating Yak Guru
400 Posts |
 Posted - 2005-02-20 : 07:38:15
|
I have a query that returns the student details. Now I was asked to change the query so that if the student lives with his mom then it will show her address and if he lives with his dad then his address.I have 2 tables:STUDENT TABLE with the following fields:sid-identitysfullname nvarcharaccomodation smallint-field that states with what parent the student lives-can be both parents also.PARENTS TABLE:Pid-identitysid -from student tableparenttype -which gets the value either 1-mom or 2-dad.cityI need to return only one row for each student. If the accomodation=401 then I want to return the city where parenttype=1.If accomodation=402 then city where parenttype=2.If accomodation=403 that means that he lives with both parents and therefore it can return the city where either parenttype = 1 or 2 as the city will be the same for both.
The query that I have before the new additions is similiar to this:select sFirstname, sFamilyName,Maxparent.CITYNAME from LearningstudsView inner join student on student.sid=learningstuds.sid--left outer join as not every student has a parent in table parents. LEFT OUTER JOIN (select max(parentno)AS pid , sid,CITYNAME from parents INNER JOIN TBLCITY ON CITYID=CITY group by sid,CITYNAME)as Maxparent on student.sid=Maxparent.sid
|
|
|
|
|
|
select query