got here from a course i couldnt understand this self join and Thanks to you it's all clear now. you explained it really well with the perfect examples. Keep up
THANK YOU. i have read a hundred tutorials and was stuck on the alias. starting with the from and on clauses and explaining the AS is implied and then going back to the select cracked things open for me. much appreciated.
I struggled with an intermediate leetcode sql problem I didn't want to see the solution right away so i started searchin on sql topics to full the knowledge gap but what i found is actually a video solving the problem itself fuck me
Thank you for the clear and simple explanation. I remembered seeing the Employee - Manager example somewhere a few years ago, but I couldn't recall where exactly I saw it.
At 6:38, relationship between id and manager_id is one to many which means one manager_id can have multiple employee_id to manage. However the multiple arrow sign is pointing to manager_id which should mean many manager_id can manage one employee_id. I think the arrow should be the opposite way, right?
I have a question again - Using the Aliases is really flexible and clever - when you deal with a big database - do you use any method to keep a list of the aliases that you have created ? In case they are quite many ?
Good question. I don't have a method to keep a list, but the aliases only exist for a specific query so we can see that they refer to right next to where they are defined.
So how would you expand these queries if you want to order them from top to bottom, with a level or additional spaces? So start with Clair (who has no manager), followed by a manager and his/her team, etc. And include a level column.
Good question! It's because of the way the two concepts or records are related. For example with employees and managers, an employee has one manager, but a manager can have many employees. This is a one-to-many relationship, so I've drawn it that way on the diagram. Hope that helps!
and what if there is a period validity of the structure (include year and month column) ? should i put it on the same table ? is it okay to have duplicate rows of ppl name?
Good question, I think it's OK to have on the same table.It would then be an additional condition on your join, or in the WHERE clause, to check if a record falls in a certain date range.
hi sir I have one doubt if we specify e.manager_id =m.id it will give the result only the both columns having same values right example manager_id =1 and emp_id also 1 or how does it works can you please clarify my doubt.
Great video. Is it possible to use self join and a many to many relationship simultaneously or it would work only for one to many? For example if an employee has 2 or more managers...
Good question! Yes, that is possible. Another example of using that could be if you want to capture the parents of a person, where each person could have more than one parent.
in ERD Diagram, id to manager_id relationship is One to Many, which means an employee can have multiple managers but one manager can not have multiple employees !! But it's not the case here, please clarify it.
The concept is easy and straitforwad, but times come you face a question with no clue that the solution comes with self join, unless you are given the schema
Which one, the employee and manager? I think it's correct - an employee ID can be related to many other employee manager ID fields, which indicates that an employee can manage multiple other employees. Unless I am missing something?
Want to easily remember the SQL commands for your database? Get my free SQL Cheat Sheets here: databasestar.mykajabi.com/lpkj-sql-cheat-sheets/?
I like how you talk :quiet and clear
Thanks!
Thank you, I was struggling to understand self join, but after watching your video, I realised how simple it is.
Glad it was helpful!
Man! That CATEGORIES example saved me just in time before creating many tables. Thanks
No problem, glad it was helpful!
got here from a course i couldnt understand this self join and Thanks to you it's all clear now. you explained it really well with the perfect examples. Keep up
Great to hear!
Thank you so much! I was trying to understand the ON statement in SELF JOINS and you've explained it perfectly.
Glad it was helpful!
Definitely needed this and you explained it perfectly
Thanks!
THANK YOU. i have read a hundred tutorials and was stuck on the alias. starting with the from and on clauses and explaining the AS is implied and then going back to the select cracked things open for me. much appreciated.
Thanks for the kind words! I’m glad this video helped you understand it.
Thanks for breaking this down so simply.
Glad you like it!
Even after a year you are helping guys like me! Thanks for clearing my doubt
Thanks! Glad you found it useful.
I struggled with an intermediate leetcode sql problem
I didn't want to see the solution right away so i started searchin on sql topics to full the knowledge gap but what i found is actually a video solving the problem itself fuck me
Oh is that a good thing or a bad thing?
@@DatabaseStar
I don't maybe I should've solved it on my own 😥
thank you! been lurking in your channel for a while now, great content sir!
Awesome, thank you!
I'm thankful I found this video your explanation is what I needed!
I’m glad you liked it!
Nicely explain in simple terms...checked many of your videos too... I am being SQL developer, liked your work 👍
Thanks! Glad you like the videos.
King of SQL explanation
Thanks!
somethething that i needed currently for my project. thanks a lot sir
Glad it was helpful!
Thank you for the clear and simple explanation. I remembered seeing the Employee - Manager example somewhere a few years ago, but I couldn't recall where exactly I saw it.
Glad it was helpful!
Thank you so much for this clear explanation
Glad you liked it!
This is super helpful! Your voice is almost like listen to ASMR, lol. Nice to listen to
Heh thanks- I’ve been working to improve the audio on my recent videos so the older ones may sound softer.
It's not almost like, IT IS!!!
your explaining is great thank u..and good luck
Thanks!
9:05 writing query and making er diagram is so much helpful
Glad you found it helpful
Huge thanks for such a clear explanation! Helped a lot!
Glad it helped!
At 6:38, relationship between id and manager_id is one to many which means one manager_id can have multiple employee_id to manage. However the multiple arrow sign is pointing to manager_id which should mean many manager_id can manage one employee_id. I think the arrow should be the opposite way, right?
Ah yes you're right! The arrow should be the other way around. Thanks for letting me know.
@@DatabaseStar Hi! Sry I think you were correct in the video, Since the foreign key is always on the many side in a EDR. Right?
I have a question again - Using the Aliases is really flexible and clever - when you deal with a big database - do you use any method to keep a list of the aliases that you have created ? In case they are quite many ?
Good question. I don't have a method to keep a list, but the aliases only exist for a specific query so we can see that they refer to right next to where they are defined.
wow amazing explanation thanks!!
Glad it was helpful!
So how would you expand these queries if you want to order them from top to bottom, with a level or additional spaces?
So start with Clair (who has no manager), followed by a manager and his/her team, etc. And include a level column.
Good question. It depends on the database vendor, and I've written more about it here: www.databasestar.com/hierarchical-data-sql/
Thank you for this!
Glad it helped!
Great explanation just one doubt in the ER diagram for both examples of self join why did you show 1:many relationship?
Good question! It's because of the way the two concepts or records are related. For example with employees and managers, an employee has one manager, but a manager can have many employees. This is a one-to-many relationship, so I've drawn it that way on the diagram. Hope that helps!
Great content! Awesome channel!
Thanks so much!
Brilliant explanation
Glad you liked it!
Nice & clear
Thank you
and what if there is a period validity of the structure (include year and month column) ? should i put it on the same table ? is it okay to have duplicate rows of ppl name?
Good question, I think it's OK to have on the same table.It would then be an additional condition on your join, or in the WHERE clause, to check if a record falls in a certain date range.
really good video
Thanks!
lovely example
Thanks!
Great explanation!
Thanks!
Very clean, thank you
Thanks!
hi sir I have one doubt if we specify e.manager_id =m.id it will give the result only the both columns having same values right example manager_id =1 and emp_id also 1 or how does it works can you please clarify my doubt.
Yes, if you match on e.manager_id = m.id it will show employers and their managers, which is correct.
Great video
Thanks a lot!
Perfect explanation thanks a lot
You are welcome!
Great video. Is it possible to use self join and a many to many relationship simultaneously or it would work only for one to many? For example if an employee has 2 or more managers...
Good question! Yes, that is possible. Another example of using that could be if you want to capture the parents of a person, where each person could have more than one parent.
in ERD Diagram, id to manager_id relationship is One to Many, which means an employee can have multiple managers but one manager can not have multiple employees !! But it's not the case here, please clarify it.
Good point, the employee can only have one manager but the manager can have many employees.
@@DatabaseStar Thanks for clarification.
04:45 the ON clause is what you need to know the rest is bloatware
Thanks for the comment!
The concept is easy and straitforwad, but times come you face a question with no clue that the solution comes with self join, unless you are given the schema
Thanks! That is a good point.
What a video!!! Thanks :)
Glad you liked it!
No wonder why you are calling yourself a database star 🔥🔥🔥🔥😁
Thanks! The name is more for my readers and viewers to become "database stars" by learning and improving :)
@@DatabaseStar 😁😁🔥🔥🔥🔥🔥🔥🙏🏻🙏🏻
thanks for the content !!
No worries!
Good tutorial
Glad you think so!
Thanks. It was 👍
You're welcome!
Thank you.
You're welcome!
Thank you sir
You're welcome
Thank you sir
Glad it helped!
Perfect
Thanks!
self joins make my brain melt
Yes they are a hard concept to understand!
It is okay
Thanks!
I think your ERD should be the other way around
Which one, the employee and manager? I think it's correct - an employee ID can be related to many other employee manager ID fields, which indicates that an employee can manage multiple other employees. Unless I am missing something?
I love you
Haha thanks!
❤
Thanks
Hey, the employee example was good! I waited till end for another example tho.. 🥲
Thanks! Good point, I should have included more examples.
Thank you.
You're welcome!