May 6, 2014 at 1:07 am #169300
Anyone have a good resource to learn about Joins? I find it confusing, particularly which join to use (inner, outer, left, right) to join two tables. My tables are setup like this:
- provider (Twitter or Facebook)
I’m not even sure if my tables are efficient. I chose to put the user ID in both that way I could distinguish who wrote the comment. Then, display the person’s name, image and link by joining the
userstable with the
commentstable on output to the page.
I’ve read a few times about a foreign key when joining tables. No idea what that is or if I should be using it.May 6, 2014 at 4:25 pm #169363
As Ben has linked, those are good resources.
I personally like to give my unique keys unique names – something like user_id, or company_id, this way when creating joins, it’s very easy to see what connects to what.
I do personally take it a step further and make all columns have unique names to a certain extent, sort of ‘namespacing’ them in a way. This allows for joining multiple foreign keys, etc. a simple thing.
My table structure would like more like this:
- user_id (key)
- comments_id (key)
- page_id (I don’t know exactly what this column is, I assume it’s a foreign key?)
Here’s a join to show you all comments made by a user (assuming user_id = 1)
SELECT * FROM comments c JOIN user u ON u.user_id = c.user_id WHERE u.user_id = 1
Want to show all users that have made a comment?
SELECT * FROM user u RIGHT JOIN comments c ON u.user_id = c.user_id
Using the right join here will only show you the users that are also in the comments table.
These are just some simple examples, hope that helped!May 6, 2014 at 5:30 pm #169373
I wouldn’t have a users.uid. I would make users.id a unique key and then make comments.uid a foreign key referencing users.id.
@BenWalker A bit confusing. Are you saying I should do away with
users.uidand just make the USER ID as
Right now, the
user.idis just an auto incrementing column. I probably don’t need this now that I think about it because the users ID will be unique.May 6, 2014 at 5:35 pm #169374
page_id (I don’t know exactly what this column is, I assume it’s a foreign key?)
page_idis basically a post ID in which it tells me on which article the comment was posted.May 6, 2014 at 5:42 pm #169377
OK, so my example still stands as is :)May 6, 2014 at 6:41 pm #169382May 6, 2014 at 6:57 pm #169383
They are aliases (so you don’t have to type out the whole table name, which can get tedious in some cases and make your code harder to read).May 6, 2014 at 7:23 pm #169385May 7, 2014 at 1:57 pm #169474
Actually it is INNER JOIN which will give you matching records from both tables.
I didn’t want records from both tables, I wanted those users that have made a comment.May 8, 2014 at 7:08 am #169550
Thanks for the clarification :)March 15, 2015 at 6:30 pm #198259
how to count table1 records from inner joining table2 records..?
You must be logged in to reply to this topic.