CODEWITHSUNDEEP
mysqldatabasejoin
henhen
henhen

Reputation: 1205

MySQL joins and pulling correct data for a user

I am currently reading about inner joins and left joins on W3Schools and was wondering how to pull the correct data for a user. From what I have read so far from sql_join_left and sql_join_inner, these joins pull out all data from the tables. The inner joins pull all matching data from both tables while the left join pull all matching data only from the left table. If I have a user register on my application and the user joins a thread and leaves comments, would the only way to retrieve the correct comment history for that user be to send back the users Primary Key as a reference to the users ID after registration, then loop through all the data I get back with a for loop until the users ID matches the ID I set for comment table.

My Users table has columns Primary Key, First Name, Last Name, Password, Email.

My Comments table has the columns, Primary key, Comment, and User ID (this is the primary key the user is assigned after registration and I will be using this column to match with the primary key in the Users table in the for loop. Also since this column is a reference to the users Primary Key in the Users table, it is okay to have multple IDs that are the same which will be referencing the same user?)

Would this be the correct approach to retrieve the correct data for the user or can it be pulled specifically with a query and not after I get a response of all the data and use a loop to match Users Table primary key to the Comments table Users ID

Upvotes: 0

Views: 66

Answers (2)

Daniel Tran
Daniel Tran

Reputation: 6171

Your tables are correct, so you will have Users and Comments tables, which Comments table will have UserId that referred to Users table primary key.

That's called one-to-many relationship. (mean 1 user can have multiple comments). And it's OK to have multiple userIds in comments table, that referred to 1 single user in Users table.

Depend on the use case you can use join or not, for example, if you already have the user, you want to get all the comment, simply says:

SELECT * FROM COMMENTS WHERE UserId=%UserId%

If, you want to get top 10 comments, with the user Informations, you can use INNER JOIN.

SELECT Users.UserName, Comments.* 
FROM Comments
INNER JOIN Users
ON Users.UserId == Comments.UserId
LIMIT 0, 10;

Then you get the comments data along with the corresponded user information.

Upvotes: 1

Yemi Tula
Yemi Tula

Reputation: 29

This answer (https://stackoverflow.com/a/6188334/5580417) provides a really good explanation for MySQL Joins.

For your specific challenge, you do not need to load all comments and then loop through to filter for the user's specific comments. In fact, in this case you don't really need a JOIN. You simply need to query the comments table using your Foreign Key (User ID). A query like this would work:

SELECT User_ID, Comment FROM Comments WHERE User_ID = '165'

This gives you User 165's comment history.

Upvotes: 0

Related Questions