Self join in sql server - Part 14
Vložit
- čas přidán 22. 08. 2024
- In this video we will learn about
1. Self Join - Joining a table with itself
2. Self Join is not a different type of join. It can be classified as Inner Self Join, Outer Self Join (Left, Right and Full), or Cross Self Join.
Text version of the video
csharp-video-tu...
Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our CZcams channel. Hope you can help.
/ @aarvikitchen5572
Slides
csharp-video-tu...
All SQL Server Text Articles
csharp-video-tu...
All SQL Server Slides
csharp-video-tu...
All Dot Net and SQL Server Tutorials in English
www.youtube.co...
All Dot Net and SQL Server Tutorials in Arabic
/ kudvenkatarabic
In preparation for the 70-761 exam, I found this to be an excellent source! Very clear structure with a high pedagogic value!
hope u did well :)
hands down THE BEST SQL TUTORIALS on youtube very consistent and clear
I learned most of the SQL functions through this tutorial.
Thanks for posting.
You probably dont give a shit but if you are stoned like me during the covid times then you can watch all the new movies and series on InstaFlixxer. Have been watching with my brother lately :)
@Alexander Cyrus Yup, been watching on InstaFlixxer for since november myself :D
@Alexander Cyrus yea, have been watching on instaflixxer for months myself :)
GOD BLESS YOU! Have a nice day. These videos are wonderfull. You are great teacher.
Greetings from Poland!
poland? wow
thank you sir, I watched two videos on this topic and couldn't grasp the whole idea behind it. But your explanation made it crystal clear, as usual. Thanks again sir and take care👍👍👍
Venkat, your explanations and videos are so well structured that they make the whole course look relatively easy!!! Many many thanks from Brussels
I want to thank for these superb tutorials. whenever I want to learn something on SQL I straight come to these tutorials. Thanks for sharing your knowledge with us.
Hello Swati - Thank you very much for taking time to give feedback. This means a lot. I am very glad you found the videos useful.
This lectures help me a lot to understand the SQL.
I have started to apply it. Thank you very much.
Was struggling to solve this on my own. Your video worked liked a charm. God bless you.
+rajan77 Thank you so much for the feedback. Means a lot. Glad the videos are useful.
Dot Net & SQL Server training videos for aspiring web developers
czcams.com/users/kudvenkatplaylists?view=1&sort=dd
Download our training videos and slides for offline viewing
www.pragimtech.com/Order.aspx
Code Samples, Text Version of the videos & PPTS on my blog
csharp-video-tutorials.blogspot.com
Tips to best utilise our channel
czcams.com/video/y780MwhY70s/video.html
To receive email alerts, when new videos are uploaded, please subscribe to our channel using the link below
czcams.com/users/kudvenkat
Please click the THUMBS UP button below the video, if you think you liked them
Thank you for sharing the link with your friends who you think would also benefit from them
Best
Venkat
Hi Rajesh, I think it is because, you have a comma(,) after M.NAME as MANAGERID. Remove the comma and execute the query again. Please let me know, if that has fixed the issue. If not, please let me know, what error message you get.If you want to receive email alerts, when new videos are uploaded, please feel free to subscribe to my youtube channel. If you like these videos, please click on the THUMBS UP button below the video. Good Luck.
Thank you so much, I would like to say that you are the best one ever explained self join. many many thanks.
Excellent video. I was wondering why we have to join a table to itself. Your explanation is very clear. Thank you so much.
This is the best explanation of self join I have found. Thank you!
Simply Excellent!!! I don't understand why 51 dislikes.. for this beautiful and elegant explanation.
51 other educators who could not clarify joins for us
sir, ur voice is so good. I can sit and listen and learn whole day
Your Explanation Clears my Concept of JOINS... Thanks a lot Sir.. and keep Updating Other Topics also..Thank you
WHy did we use E.ManagerID = M.EmployeeID why not vice versa?
Not widely discussed but better explained!
Explained nicely in precise time.
Content presentation is awesome.
I enjoyed learning.
Thanku so much sir.
If I have to get the employee names and their respective manager names in the same row, I have to join the Employee table with itself. This is because both the employee and manager details are present in the same table. This is one typical scenario for joining a table to itself, in this case Employee table. Please let me know, if this answers your question.
Yup they interviewed this question and I left it blank lol
Your teaching method is wonderful. Thank you.
always there is an alien who dislike something great , you are great sir
Hi Venkat, excellent training materials. It would clarify a lot of things if you elaborated a little more on using Alias here :)
Thanks for all your tutorials on sql server.... Really good job and Great helpful....
You're a very good teacher. Thank you!
whats the difference between e.ManagerID=m.EMployeeID and m.ManagerID=e.EMployeeID
There is no difference. You are creating a duplicate table by assigning alias. For one table it is 'm' and for other it is 'e'.
you are genius man.....
🙏🙏🙏🙏
great job venkat
Thank you very much for taking time to give feedback. In the description of this video, I have included the link for ASP .NET, C#, and SQL Server playlists. All the videos are arranged in logical sequence in these playlists, which could be useful to you. Please share the link with your friends who you think would also benefit from them. If you like these videos, please click on the THUMBS UP button below the video. For email alerts, when new videos are uploaded, you may subscribe to my channel.
You are doing excellent job and service to people. Sure. I have subscribed to the channel.
Hi there,
This does help a lot. I am having a specific challenge where I'm trying to work around JIRA and confluence limitations. In Confluence we can create a report from JIRA using Table Transformer.
What I have is a table with all the issues from a JIRA Project and it has a hierarchy of EPIC >> TASK/STORY/SPIKE >> SUBTASK
I have a massive amount of trouble trying to export and work with a report that lists out the tasks in a logical order, i.e. everything just comes out in a random order depending on the issue "Key" which is the primary key for the table. The subtasks are linked to tasks, stories or spikes by parent id column and in turn the tasks, stories and spikes are linked to the epics by parent id.
So a table might look like this:
Issue key Summary Issue Type Status Assignee Parent
ISS-1 Task 1 Task To Do bob ISS-10
ISS-27 Task 2 Task Done alice ISS-10
ISS-32 Task 3 Task Done john ISS-10
ISS-33 Task 4 Task Done mark ISS-15
ISS-16 Task 5 Task To Do bob ISS-15
ISS-15 Epic 1 Epic To Do
ISS-24 Epic 2 Epic To Do
ISS-10 Epic 3 Epic To Do
ISS-37 Subtask 1 Subtask In Progress alice ISS-1
ISS-38 Subtask 2 Subtask To Do john ISS-1
ISS-39 Subtask 3 Subtask To Do mark ISS-27
ISS-40 Subtask 4 Subtask In Progress alice ISS-27
ISS-45 Subtask 5 Subtask In Progress john ISS-16
ISS-41 Subtask 6 Subtask Done mark ISS-16
ISS-66 Spike 1 Spike To Do alice ISS-24
ISS-67 Story 1 Story To Do john ISS-24
ISS-69 Subtask 7 Subtask To Do mark ISS-66
ISS-72 Subtask 8 Subtask To Do alice ISS-66
ISS-70 Subtask 9 Subtask To Do john ISS-66
ISS-71 Subtask 10 Subtask To Do mark ISS-67
ISS-74 Subtask 11 Subtask To Do alice ISS-67
ISS-73 Subtask 12 Subtask To Do john ISS-67
And I want the output to read:
Issue key Issue Type Summary Status Assignee Parent
ISS-15 Epic Epic 1 To Do
ISS-33 Task Task 4 Done mark ISS-15
ISS-16 Task Task 5 To Do bob ISS-15
ISS-45 Subtask Subtask 5 In Progress john ISS-16
ISS-41 Subtask Subtask 6 Done mark ISS-16
ISS-24 Epic Epic 2 To Do
ISS-66 Spike Spike 1 To Do alice ISS-24
ISS-69 Subtask Subtask 7 To Do mark ISS-66
ISS-72 Subtask Subtask 8 To Do alice ISS-66
ISS-70 Subtask Subtask 9 To Do john ISS-66
ISS-67 Story Story 1 To Do john ISS-24
ISS-71 Subtask Subtask 10 To Do mark ISS-67
ISS-74 Subtask Subtask 11 To Do alice ISS-67
ISS-73 Subtask Subtask 12 To Do john ISS-67
ISS-10 Epic Epic 3 To Do
ISS-1 Task Task 1 To Do bob ISS-10
ISS-37 Subtask Subtask 1 In Progress alice ISS-1
ISS-38 Subtask Subtask 2 To Do john ISS-1
ISS-27 Task Task 2 Done alice ISS-10
ISS-39 Subtask Subtask 3 To Do mark ISS-27
ISS-40 Subtask Subtask 4 In Progress alice ISS-27
ISS-32 Task Task 3 Done john ISS-10
I've tried a few self joins but just can't seem to get the logic working.
Hello Venkat, it would be great if u can answer the query of Sangam chandraprakash.Thanks a lot for your wonder full videos.
Very clear and helpful explanation! Thanks a lot!
Thank you for great videos. Are u using SQL express to make all videos?
hello sir,
I am very much happy by watching your videos. Great work. I have a small doubt regarding self join. When we doing the self join on one table do we need to create any constraint between the EmployeeId and ManagerId.
Thank you.
Best 💯 explanation
amazing explanation, thank you very much
Awesome.......Thank you so much, we are expecting more of such class, do keep posting
It's very helpful. Thank you.
Thank you!!! Very good at explanation!!!
Great Job kudvenkat Best regards from Germany! Yo The way how you explain your Videos is amazing!
Hi Venkat. In this tutorial how come the M.Name takes the value as ManagerID??
Since E = M, E.Name should be equal to M.Name.
Kindly Explain.
Good videos, thanks for sharing.
TERRIBLE RESULT WHEN WE USING
"SELECT
* FROM TABLE NAME E
Right JOIN TABLE NAME M
ON M.EMPPiD = E.MANID"
u are brilliant thnx for ur videos
Hi Venkat.. Great Job.i have question .. how to use aggregate function in self join.
thanks a lot man. you explain super well
May i know what is the use of cross joins or when we should use them?
Excellent presentation. Thank you so much.
what is the diff between e.ManagerID=m.EMployeeID and m.ManagerID=e.EMployeeID
because i m getting different result for these two ..
Great explanation. Thanks much
a very good tutorial, but i have a question if any one can help me?
why the results of those two queries are not the same :
1- select E.[Name] as Employess,M.[Name] as [manager name]
from tblemployee E
left join tblemployee M
on E.ManagerID=M.EmployeeID --this will show a correct result
2- select E.[Name] as Employess,M.[Name] as [manager name]
from tblemployee E
left join tblemployee M
on E.EmployeeID=M.ManagerID -- but this one display wrong result
you said LEFT JOIN will bring us all the records matching from both the tables plus non-matching rows from the LEFT table. But I read that the non-matching records are matched from the RIGHT table in LEFT JOIN. Are you sure for your statement?
Thank you Venkat!
Yes this is awesome. i have learned very easily. thank you so much
Great Video, though I would like to know the difference between applying join conditions, for the example illustrated here, applying e.ManagerID=m.EMployeeID gives the correct result, however if we interchange i.e. m.EmployeeID=e.ManagerID, we could not achieve the expected results.Could someone, please explain what's the reason for this.Thanks!
Hi venkat u did a gr8 job with the videos..
Do u know where can i find sql exercises along with answers... Tnq
+VEnky nv take a look at Korth Reference book i think it might include SQL excercises for solution i guess google will help.
Awesome !
Thank you!
Sir if you make it a bit elaborate then it will really help full to us..it took so long time to understand it with various query..
Good job sir
Hi Venkat,
Can you please clarify Right outer self join how it gives 7 records.
Venkat thanks for videos, I really appreciate your efforts I just want to know weather have you recorded an videos on SSIS,SSRS,SSAS if so please provide the link ASAP. Thanks
Hello Daniel - Sorry, at the moment, we don't have videos on SSIS, SSAS and SSRS.
@@Csharp-video-tutorialsBlogspot Can You Please Do Videos On SSIS It Will Help Us a Lot.
super boss...thanks for the tutorial
what if the columns in left or right join, that is with no match, have a default constraint. Whether they are filled with NULL or that default value?
Question: what if we use right join here? Will cause error or not?
For full outer join is it give 8 records???
(4 common, 1 left non-common and 3 right non-common).
Very Helpful! Thanks!
hey! i have a question. You asked to refer to part's 12 and 13.. do you actually mean to use the same employee table as in part 12 and 13 ? or create a new table ?
Can you add a video on how to create a select statement on Outer Joins without using JOIN keyword in SQL ? Thanks in advance....
sir, please explain right self outer join in this above table
Quick question, so are you referencing a foreign key constraint for ManagerID to EmployeeID in this table?
Is it possible to have foreign key constraint within the same table?
Good question, I don't think so, because one of the ManagerID values has NULL and a Primary Key can't have NULL as a value, a primary key column must contain unique values. So it's not possible that ManagerID is a FK to PK EmployeeID
very very helpful
Thank you
Hello Kudvenkat, I don't get how your tblEmployee table was created. My query results return an error, "EmployeeId" colum name invalid. I used the table created in parts 12&13. Please let me know if this tblEmployee table is different from the ones used in parts 12&13 tutorials.
Thanks
Great.....
What is the data type of the ManagerID? =)
If you are joining a table to itself for the reason listed in this video doesnt that mean it isnt in normal form?
Dude, you rock!
thanks for the videos,
I tried doing the ON condition reverse,
I got a different result set with additional columns,
I am having a hard time understanding this new result, Can you please explain what happened here?
did you get answer I am also stuck there if u have answer please tell me
Can someone explain why in the Select portion of the code you write M.name as Manager instead of M.ManagerID? I'm kind of confused. How does M.Name work because wouldn't that just give you the name of the employees again since tblEmployee E and tblEmployee M is identical? Pls I don't understand...
Excellent
Thank you sir
Thank you, this is wonderful :-)
Nice...
hi sir, will you please provide scripts for tables u have used so that we can practice
hello venkat, great work, i am stuck here do i have to make another two tables and link those column via foreign key ? because i was following all the tutorials 1-13 i think i will have to change the structure of tblEmployee table am i right ? because i am having error on executing the query using the same tblEmployee as in previous video No. 13
I thought I had been studying continues for long hours, taking a break will solve the problem .......... and yes it did hahahaha, I surly had to make a new table and the table which Venkat is showing in the starting of this video. I dont think anyone else here is dumb like me but if i am lucky to get one then this self answer is for you if you are following Venkat Video then the tblemplyee tabe created previously has nothing to do with this tblemployee you will have to make a new one as shown in video. Thank you Venkat :)
Nice Work!!
If I have one Table 'Employee ' where EmployeeID, EmployeeName, FirstName, LastName, ManagerID, ManagerName here ManagerID have also EmployeeID. Employee have work under Manager. How do I find from table 'Employee' FirstName, EmployeeID, ManagerID, ManagerName where every manager have not more than 6 employees.
Which join is faster and why?
This is amazing
THANK YOU SO MUCH!
Mr.Venkat when i'm using E.Name its giving me a error called invalid column name 'Name'. I have created a new table called tblManager. the query which i wrote is as follows
select E.Name as Employee, M.Name as Manager
from tblManager E
inner Join tblManager M
ON E.[Manager ID] = M.[Employee ID]
+sai avinash edara your query is wrong. After "from" the table name should be tblEmployee. Here in this query you are referring only tblManager & never tblEmployee at all...!!!
So good whn u watch at 1.5z
Excelent buddy , thanks a lot!
select E.NAME as EMPLOYEEID, M.NAME as MANAGERID
FROM TBLEMPLOYEEID E
INNER join TBLEMPLOYEEID M
on E.EMPLOYEEID=M.MANAGERID
whats the result if i do that? i just xchanged last line employeeid with managerid and vice versa.
thank u
how to write department wise, job wise ,total salary in self join
You've flew so high ! are you living in UK ?
super sir
From where i can get data for practice.
thank u