The forums ran from 2008-2020 and are now closed and viewable here as an archive.

Home Forums Back End MySQL Joins

  • This topic is empty.
Viewing 11 posts - 1 through 11 (of 11 total)
  • Author
  • #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:

    Users table

    • id
    • uid
    • name
    • image
    • link
    • provider (Twitter or Facebook)

    Comments table

    • id
    • uid
    • comment
    • page_id
    • date

    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 users table with the comments table 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.


    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:

    Table user:

    • user_id (key)
    • user_name
    • user_image
    • user_link
    • user_provider

    Table comments:

    • comments_id (key)
    • user_id
    • page_id (I don’t know exactly what this column is, I assume it’s a foreign key?)
    • comments_text
    • comments_date

    Here’s a join to show you all comments made by a user (assuming user_id = 1)

        comments c
        user u ON u.user_id = c.user_id
        u.user_id = 1

    Want to show all users that have made a comment?

        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!


    I wouldn’t have a users.uid. I would make a unique key and then make comments.uid a foreign key referencing

    @BenWalker A bit confusing. Are you saying I should do away with users.uid and just make the USER ID as

    Right now, the is just an auto incrementing column. I probably don’t need this now that I think about it because the users ID will be unique.



    page_id (I don’t know exactly what this column is, I assume it’s a foreign key?)

    The page_id is basically a post ID in which it tells me on which article the comment was posted.


    OK, so my example still stands as is :)


    @shaneisme @BenWalker Thanks, guys!

    One question. Your example code does not seem to make sense to me. Particularly where you wrote comments c and user u. What is c and u?


    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).


    Thanks @traq.


    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.


    Thanks for the clarification :)


    how to count table1 records from inner joining table2 records..?

Viewing 11 posts - 1 through 11 (of 11 total)
  • You must be logged in to reply to this topic.