problem with left join in sql query

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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 23-Sep-14 23:34pm

manvendra patel

Add a Solution

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

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Gihan Liyanage · Accepted Answer · 2014-09-23T23:58:00

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

Animesh Datta · Accepted Answer · 2014-09-23T23:58:00

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

problem with left join in sql query

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0