Thanks for the lucid explanation. Summary: 1. SELECT columns to show from the first table 2. (INNER/OUTER) JOIN to the second table 3. add the columns to join ON 4. add any additional columns to step 1. 5. (INNER/OUTER) JOIN to third table 6.add the columns to join ON . The columns can belong to the first/second table and then join it with the column for the third table 7. add any additional columns to step 1.
I have been trying to make sense of joins for the past 4 weeks - but the very essence of it had eluded me. NOW I understand! Thank you for simplifying this to the point where even I could understand it!
Thank you! I have watched so many videos where I get completely lost. I was able to follow along in the order and understand how! I really appreciate you and the method you teach.
Needed to turn a setting off on certain records in one table based off of a category stored in another table. I'm just beginning to learn SQL for my job and this helped!
The diagram at the beginning of the table is misleading. It shows it as A join B, B join C and A join C. The query however is just two joins, A join B, A join C. I was expecting the video to be dealing with a circular join. Thank you and keep them good videos on complex data modeling concepts coming.
Is this standard SQL, PL/SQL or T-SQL? Or it does not matter? :-) Thanks for the video! I just failed an interview with this EXACT question. Feeling awful. But life goes on!
This is standard SQL. The inner join, left join, and right join are all part of the SQL standard. There are some extra join features in specific database types, but I don't think I mentioned any of them in this video.
Excuse me Sir I have a question. So in step 5 and 6, when joining the 3rd table and later specifying the target columns, suppose now I want to join based on the columns on the 2nd table & the 3rd table, not the 1st table & the 3rd table. What decides which join type to use? In this case does the sequence of specifying table names matter? What if there're more than 3 tables? Thank you in advance!
Good questions! - When joining the 3rd table, you can join to any column in either the 1st table or 2nd table. It depends on how you write the query and add the columns and what makes sense for your data. - The results that you need will determine the join type to use. I see either an INNER JOIN or LEFT JOIN most commonly, depending on whether you want to see rows that have matches or not. - The sequence that you specify the tables will not matter for an INNER JOIN, but it will matter for a LEFT JOIN and RIGHT JOIN. - If there are more than 3 tables, you repeat the process: join to table 4, then specify the join condition to either table 1, 2, or 3.
I’m using SQL management server and I need with the scenario, I have table A, junction table and Table B I need one row for table A and concatenate multiple rows of table b into one row with a comma. I’ve search the hours and can find no clues at all. I’d really appreciate it if you help me out
Hey, I think you would need to use a technique called “string aggregation”. This is done using a function called string_agg in sql server. If you have the right results in separate rows showing for table a and b, you can then use this function to combine rows from table b into one row.
Thanks! No, the result should stay the same if you are using an Inner Join. If you're using an outer join (e.g. left join or right join) then yes the result will change.
You should be able to use the same process. You can specify the column name in the ON clause of the join. For example, if table 2 has a column called some_other_id: JOIN table2 ON table1.id = table2.some_other_id
The final result is shown from about 3:16 where the query has fields from three different tables, which is the example. Were you looking for something else?\
Ah that’s because the column name exists in more than one table and the database does not know which column you want. If you add the table name and a . Before the column it should work. Eg customer.firstname instead of just firstname.
This video saved me. You explained this better than a text book, 15 minutes video, and a lecture from my prof combined did. Thank you
Glad it helped!
Thanks for the lucid explanation.
Summary:
1. SELECT columns to show from the first table
2. (INNER/OUTER) JOIN to the second table
3. add the columns to join ON
4. add any additional columns to step 1.
5. (INNER/OUTER) JOIN to third table
6.add the columns to join ON . The columns can belong to the first/second table and then join it with the column for the third table
7. add any additional columns to step 1.
Thanks for the summary!
Good man, good man...
I have been trying to make sense of joins for the past 4 weeks - but the very essence of it had eluded me. NOW I understand! Thank you for simplifying this to the point where even I could understand it!
Thanks! I'm glad you found it useful and are able to understand it!
hours upon hours of trying to figure this stuff out and your short video was all i needed. wish I could have found it sooner. THANKS!!
Glad you found it helpful!
Me too!
Thank you! I have watched so many videos where I get completely lost. I was able to follow along in the order and understand how! I really appreciate you and the method you teach.
Thanks! Glad it helped you understand it.
Thank You! I've been banging my head against a wall trying to figure this out. This video finally helped me. Many many thanks!
Glad it helped!
Clear and simple presentation, thank you very much
You are welcome!
Extremely easy to understand. Thank you so much for this!!!!
Thanks, glad you liked it!
Thank you so much! Just 2 words, "Amazing Explanation"
Thanks a lot!
Ive been rattling my brain for over an hour...to do what you did in 4:05....thank you
I'm glad it has helped!
Thank you sir, could not have finished my MySQL project without. Thank you!!!!!!!!!
Glad it helped!
Needed to turn a setting off on certain records in one table based off of a category stored in another table. I'm just beginning to learn SQL for my job and this helped!
Glad it helped!
Great video, still very relevant after 2 years!
Glad it was helpful!
Thanks so much! This saved me! Love from Sri Lanka
Glad it helped!
Thank you, thank you, thank you!!! Very clear!
Glad it was helpful!
Thanks for the illustrated example
Glad you like it!
Saved my life with this one
Great to hear!
You are a life saver, I am assuming this can be applied for even more than 3 tables, I definitely need to master MySQL for college projects, tysm.
Thanks! Yes you can use the same process for as many tables as you need.
Thanks looks so easy now, well explained
Thanks!
i enjoyed the video.thank you dad
Thanks!
Thanks, very easy to understand. :D
Glad it helped!
Great explanation
Glad it was helpful!
It really helpful. Thanks
Glad it was helpful!
Thank you thank you! This helped me with a project for school. Thank you a ton!
Awesome! Glad it helped.
@@DatabaseStar I ended up passing the project. Again, thank you.
Fantastic! Thank you!
You’re welcome!
Thank you so much!
You're welcome!
THANK YOU SO MUCH!
Glad it helped!
Thank you!
No problem!
it would be great if you could include what the tables originally looked like and how they look after each statement.
Thanks for the feedback! I’ll keep that in mind for future videos. I’ll see if I can add something for this to the Description area.
Lifesaver thank you sooooo much
Glad you found it helpful!
Really specific and helpful,thank you
Glad it was helpful!
Mate I wish I had this back in uni.
Thanks! Yeah me too, I’m glad it’s helping many people.
I learned something new
That’s great to hear!
Very helpful, thankyouuuu
Glad it was helpful!
thanks a lot ❤️
You’re welcome!
The diagram at the beginning of the table is misleading. It shows it as A join B, B join C and A join C. The query however is just two joins, A join B, A join C. I was expecting the video to be dealing with a circular join. Thank you and keep them good videos on complex data modeling concepts coming.
That's a good point! I can update the thumbnail to make it clearer (I don't think I can update the video), but this is good to know.
thanks to your video
Glad you found it useful!
Thank u 👍
You’re welcome!
Is this standard SQL, PL/SQL or T-SQL? Or it does not matter? :-) Thanks for the video! I just failed an interview with this EXACT question. Feeling awful. But life goes on!
This is standard SQL. The inner join, left join, and right join are all part of the SQL standard. There are some extra join features in specific database types, but I don't think I mentioned any of them in this video.
Steps make it so easier
Step 5
That's true!
Excuse me Sir I have a question. So in step 5 and 6, when joining the 3rd table and later specifying the target columns, suppose now I want to join based on the columns on the 2nd table & the 3rd table, not the 1st table & the 3rd table. What decides which join type to use? In this case does the sequence of specifying table names matter? What if there're more than 3 tables? Thank you in advance!
Good questions!
- When joining the 3rd table, you can join to any column in either the 1st table or 2nd table. It depends on how you write the query and add the columns and what makes sense for your data.
- The results that you need will determine the join type to use. I see either an INNER JOIN or LEFT JOIN most commonly, depending on whether you want to see rows that have matches or not.
- The sequence that you specify the tables will not matter for an INNER JOIN, but it will matter for a LEFT JOIN and RIGHT JOIN.
- If there are more than 3 tables, you repeat the process: join to table 4, then specify the join condition to either table 1, 2, or 3.
@@DatabaseStar Thank you Sir for your answers, it makes sense! And thank u for your prompt reply!
I’m using SQL management server and I need with the scenario, I have table A, junction table and Table B I need one row for table A and concatenate multiple rows of table b into one row with a comma. I’ve search the hours and can find no clues at all. I’d really appreciate it if you help me out
Hey, I think you would need to use a technique called “string aggregation”. This is done using a function called string_agg in sql server. If you have the right results in separate rows showing for table a and b, you can then use this function to combine rows from table b into one row.
Hello sir... Thanks for making this video. Result will get change if order of the tables changes? Pls reply. 🙏
Thanks! No, the result should stay the same if you are using an Inner Join. If you're using an outer join (e.g. left join or right join) then yes the result will change.
@@DatabaseStar.. Thank you sir.
How to add table with different column name
You should be able to use the same process. You can specify the column name in the ON clause of the join. For example, if table 2 has a column called some_other_id: JOIN table2 ON table1.id = table2.some_other_id
is language id the same number as publisher id in your example??
No, in this example they are different fields.
@@DatabaseStar so how are they equal to each other if they are not the same number type?
Please I did some but data do not display in the table
It could be because there is no matches based on your join criteria
@@DatabaseStar thank you very much, it is now working
How about show the result in a final form? Your title says with example. However no example....
The final result is shown from about 3:16 where the query has fields from three different tables, which is the example. Were you looking for something else?\
Like how can i get my tittle
hm, I am getting "ambiguous column name" error.
Ah that’s because the column name exists in more than one table and the database does not know which column you want. If you add the table name and a . Before the column it should work. Eg customer.firstname instead of just firstname.
That worked! Thanks for responding and clarifying!
This query wouldnt work lol
Oh, what error are you getting?
Lol show inner with left join . Don't show easy one
Good point. Left join would be the same query with Left instead of Inner. But the results would be different
Thank you!
You're welcome!