How to optimize SQL query ?

Question

0.00/5 (No votes)

See more:

I have 3 tables like below

Student 
   	stdId
	stdName
        stdCourseID
StudentCources
        stdCourseID
        courseName
        stdeventID
StudentEvents
        stdeventID
        eventName

i want to fill all these properties like below

stdId
stdName

stdCourseName

stdEventName

when i try with this query i am getting duplicate stdId , stdName and stdCourseID follewd by list of remaning items corseName and eventName

SQL

select * from Student as s WITH(NOLOCK) 
join StudentCources as se WITH(NOLOCK) on sc.stdCourseID = s.stdCourseID 
join StudentEvents as se WITH(NOLOCK) on se.stdeventID = se.stdeventID  
where s.stdId=118

What I have tried:

SQL

select name,courseName,eventName from Student as s WITH(NOLOCK) 
join StudentCources as se WITH(NOLOCK) on sc.stdCourseID = s.stdCourseID 
join StudentEvents as se WITH(NOLOCK) on se.stdeventID = se.stdeventID  
where s.stdId=118

Could you please tell me how to optimize this query that should fetch all data

Posted 4-Apr-19 5:54am

DGKumar

Updated 4-Apr-19 11:14am

Nelek

v2

Add a Solution

Comments

Richard Deeming 4-Apr-19 12:18pm

You've used se as the alias for both StudentCources (should be "courses") and StudentEvents. I assume that's a typo in your question, since SQL won't allow that.

You've also joined the StudentEvents table to itself, rather than to the courses table:

on se.stdeventID = se.stdeventID

DGKumar 4-Apr-19 15:36pm

Sorry the below is the actual query. Is there any optimization required for the below query
select name,courseName,eventName from Student as s WITH(NOLOCK)
join StudentCources as sc WITH(NOLOCK) on sc.stdCourseID = s.stdCourseID
join StudentEvents as se WITH(NOLOCK) on se.stdeventID = sc.stdeventID
where s.stdId=118

1 solution

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)

James McCullough · Answer 1 · 2019-04-04T07:07:00

Solution 1

Also, if your student doesn't have a record in either the "StudentCources" [sic] table and "StudentEvents" table, it will return an empty set because you are using an implied INNER JOIN.

Posted 4-Apr-19 7:07am

James McCullough