Forums

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
    Posts
  • #169300
    chrisburton
    Participant

    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.

    #169363
    shaneisme
    Participant

    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)

    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!

    #169373
    chrisburton
    Participant

    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.uid and just make the USER ID as users.id?

    Right now, the user.id 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.

    #169374
    chrisburton
    Participant

    @shaneisme

    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.

    #169377
    shaneisme
    Participant

    OK, so my example still stands as is :)

    #169382
    chrisburton
    Participant

    @shaneisme @BenWalker Thanks, guys!


    @shaneisme
    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?

    #169383
    __
    Participant

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

    #169385
    chrisburton
    Participant

    Thanks @traq.

    #169474
    shaneisme
    Participant

    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.

    #169550
    shaneisme
    Participant

    Thanks for the clarification :)

    #198259
    miguelito
    Participant

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

Viewing 11 posts - 1 through 11 (of 11 total)
  • The forum ‘Back End’ is closed to new topics and replies.