Click here to Skip to main content
14,971,670 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hello every one ,i have two table, one is student and other is guardian . ,if assign more than one guardian with Student_Id i want get one Guardian with Student ,and also want to show student which have not any guardian . plz help me anyone .

i am also using left join query to get details.

Table Student
CSS
student_id  studentname     
4            A               
 
5            B    
        
6            C   
    
7            D     
    
8            E      
     
9            F



table guardian

GuardianId  student_id   Guardian_Name        
 1               6        Aa               
 
2                6       BAa     
         
3                7        CAa    
    
 

Result 

  student_id   studentname   Guardian_Name        
          
    6              c         BAa     
         
    7              d         CAa   
 
    8              E         Null          
     
    9              F          Null
</pre>
Posted
Comments
[no name] 24-Sep-14 5:44am
   
where is your query
King Fisher 24-Sep-14 5:56am
   
Wher is your Query? student_id 6 have 2 guardians Aa,BAa then why do you want to get the Second one BAa ? Need More info.
manvendra patel 24-Sep-14 6:12am
   
i want any one out of them for student_Id 6

Use This Query ,

SQL
SELECT 
    C.student_id,
    C.studentname,
    M.Guardian_Name
FROM 
    TableStudent C
    OUTER APPLY
    (
        SELECT TOP 1 *
        FROM TableGuardian M 
        WHERE M.student_id = C.student_id 
    ) m

Since you want to get any guardian I use Top 1 in above Query. If you want to get guardian with Maximum guardian ID for the student, then you can use this

SQL
SELECT 
    C.student_id,
    C.studentname,
    M.Guardian_Name
FROM 
    TableStudent C
    OUTER APPLY
    (
        SELECT TOP 1 *
        FROM TableGuardian M 
        WHERE M.student_id = C.student_id 
		ORDER BY
          M.GuardianId DESC
    ) m
ORDER BY 
    C.student_id ASC
   
v2
Comments
manvendra patel 24-Sep-14 6:24am
   
thanks lot its working
Gihan Liyanage 24-Sep-14 6:29am
   
Can you please mark it as answer, Then next member having same question can identified it is the solution.
manvendra patel 24-Sep-14 8:20am
   
yes why not,this is very usefull.
Hello ,
According to your requirement , you have to select the maximum guardianid from guardian table and then select its corresponding guardian name for each student . and then make the left outer join with student table .

Try this way
SELECT ST.STUDID,ST.STUDNAME,GU5.GURDIAN_NAME FROM STUDENT ST 
LEFT OUTER JOIN
(
SELECT GU3.STUDID,GU3.GURDIAN_NAME,GU4.MAXGUARDIANID FROM GUARDIAN GU3 INNER JOIN  
(
SELECT MAX(GU1.GUARDIANID)AS MAXGUARDIANID FROM GUARDIAN GU1 INNER JOIN GUARDIAN GU2
ON GU1.GUARDIANID=GU2.GUARDIANID
-- here make a self join to select the max guardian id 
)GU4 ON GU3.MAXGUARDIANID=GU4.MAXGUARDIANID 
 --now select the guardian name  from max guardianid
)GU5
ON GU5.STUDID=ST.STUDID    --last  make the left outer join with student table

thanks
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900