Awesome, my Prof. The other day you gave me tips on how to prepare for intermediate JS interview. I did the interview last Friday and boom, everything you said was spot on. I didn't feel disappointed in the way I answered at all. Am now waiting for feedback. But thanks so very much! I have recommended to my friends to watch your videos and subscribe to this channel. Blessings!
Hi Steve, good explanation but qq: based on your example, how would you update your querry to see there in one line not genre_id, but the id itself like "Fantasy", etc?
If I wanted to display the genre_title in my results then I need to include that in the original SELECT clause. That means i have to join the movies and genres tables. For example: SELECT m.movie_title, g.genre_title, m.year FROM movies AS m INNER JOIN genres AS g ON m.genre_id = g.genre_id WHERE g.genre_id = 1
We just had subqueries in vocational school and we never used the keyword "In". We learned that you should always combine subqueries with a join and make a clear connection for the Keys, to avoid cartesian product. Also when you have an aggregating function or smth in your query, would the query from the video still work? Im not sure tho
If you are using JOIN then you should be using ON to define the relationships between the tables and avoid the cartesian product. The SELECT clause gives you the starting dataset. Subqueries with IN go in the WHERE clause. This is how you filter that original dataset. The IN lets you find a single column of possible matches for one column in your original data set. Aggregate methods go in the SELECT clause. They require the GROUP BY. They are not impacted by a subquery using IN because the WHERE is done after the SELECT is finished.
in my wordpress database the data is saved in one key and one column. is it a way to get my data in column to row format? or should i make a table that saves the column id in a row
I would make a new View or Table to do that. Don't break the existing structure. Are you talking about creating a new structure with one row and an infinite number of columns?
It is another way of filtering results. Sometimes you are dynamically building the queries with your server-side script - PHP, etc. The subqueries can be an easy way to code a dynamic search with parameters that are coming from the user.
say you have a database with 2 tables. table1 named Users and table 2 named BannedUsers. users table have collumns id(Primary),name,email,password etc and BannedUsers have a column banned_users_id which corresponds to the id of a user who has been banned.ALSO, this table has some other columns which contain for example the reason that they are banned,how many days they are banned etc. so in order to display the names of banned users you would write something like this: SELECT `name` FROM `Users` WHERE `id` IN (SELECT `banned_users_id ` FROM `BannedUsers`). Of course there are many ways to do something like that without using subqueries but it's not good idea to have only one table with all the information. dont put all your eggs in one basket
And I made it... Just received an offer. Thanks once again prof.
Hello, how much it was? And what did they asked you?
Awesome, my Prof. The other day you gave me tips on how to prepare for intermediate JS interview. I did the interview last Friday and boom, everything you said was spot on. I didn't feel disappointed in the way I answered at all. Am now waiting for feedback. But thanks so very much! I have recommended to my friends to watch your videos and subscribe to this channel. Blessings!
This series is going to be lit.
Very well explained! I just needed to understand the basics, so now I can write more complex stuff. Thank you very much!
You've solved a couple of troubles I really had on my project. Thank you, Steve.
Thank you for sharing your knowledge with everyone!
0:03 by hearing you say just this one word, and I knew you're Canadian
Your voice like butter
Steve your videos are great, I always come back to them (like an ex girlfriend!)
Thanks, this is very well explained somewhat found it harder to explain to someone so I just sent a link of this video.
Hi Steve, good explanation but qq: based on your example, how would you update your querry to see there in one line not genre_id, but the id itself like "Fantasy", etc?
If I wanted to display the genre_title in my results then I need to include that in the original SELECT clause. That means i have to join the movies and genres tables. For example:
SELECT m.movie_title, g.genre_title, m.year
FROM movies AS m INNER JOIN genres AS g
ON m.genre_id = g.genre_id
WHERE g.genre_id = 1
We just had subqueries in vocational school and we never used the keyword "In". We learned that you should always combine subqueries with a join and make a clear connection for the Keys, to avoid cartesian product. Also when you have an aggregating function or smth in your query, would the query from the video still work? Im not sure tho
If you are using JOIN then you should be using ON to define the relationships between the tables and avoid the cartesian product. The SELECT clause gives you the starting dataset.
Subqueries with IN go in the WHERE clause. This is how you filter that original dataset. The IN lets you find a single column of possible matches for one column in your original data set.
Aggregate methods go in the SELECT clause. They require the GROUP BY. They are not impacted by a subquery using IN because the WHERE is done after the SELECT is finished.
in my wordpress database the data is saved in one key and one column. is it a way to get my data in column to row format?
or should i make a table that saves the column id in a row
I would make a new View or Table to do that. Don't break the existing structure.
Are you talking about creating a new structure with one row and an infinite number of columns?
Let's say if you want to combine 3 tables together using subquery, how would you do that?
I wouldn't use a subquery to do that. That is the purpose of JOINs.
Hi sir can you help me to convert join into subquery?
Huge thanks
Is subquery same as nested query?
yes. But subquery is the proper name.
Good Video, thanks
Good explaination but I do not understand what benefit there are to use it
It is another way of filtering results. Sometimes you are dynamically building the queries with your server-side script - PHP, etc. The subqueries can be an easy way to code a dynamic search with parameters that are coming from the user.
say you have a database with 2 tables. table1 named Users and table 2 named BannedUsers. users table have collumns id(Primary),name,email,password etc and BannedUsers have a column banned_users_id which corresponds to the id of a user who has been banned.ALSO, this table has some other columns which contain for example the reason that they are banned,how many days they are banned etc. so in order to display the names of banned users you would write something like this: SELECT `name` FROM `Users` WHERE `id` IN (SELECT `banned_users_id ` FROM `BannedUsers`).
Of course there are many ways to do something like that without using subqueries but it's not good idea to have only one table with all the information. dont put all your eggs in one basket
Thanks