Skip to main content

Forums

This topic contains 15 replies, has 6 voices, and was last updated by  miguelito 4 years, 5 months ago.

Viewing 15 posts - 1 through 15 (of 16 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.

    #169314

    Anonymous

    This reply has been reported for inappropriate content.

    Foreign keys are useful to avoid repeating content, as well as preventing bad input. In the schema you’ve posted above, for example, comments.uid (I assume “user id”) is meaningless without the users table. That column refers to an entry in users, so a foreign key would be appropriate.

    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.

    Bit of a mouthful, but worth reading through (mysql manual)

    Regarding joins, generally you’ll be safe with a left join. The following is a useful article on the matter:

    Understanding JOINs in MySQL

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

    #169391

    jurotek
    Participant

    This reply has been reported for inappropriate content.

    Using the right join here will only show you the users that are also in the comments table.

    Actually it is INNER JOIN which will give you matching records from both tables.

    RIGHT JOIN will give you all the records from Users and matching records from Comments.

    #169471

    Anonymous

    This reply has been reported for inappropriate content.

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

    Yes. I think you already sussed this, but you only need one unique id per user.

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

    #169538

    jurotek
    Participant

    This reply has been reported for inappropriate content.

    I wanted those users that have made a comment.

    I would still say that INNER JOIN is more appropriate for that.
    RIGHT JOIN maybe give you what you want, but it should be used only if you want to check for orphaned records on M side of relationship or between PK of look up table on I side of relationship which supplies row source values to combo box object (FK) on M side of relationship.

    #169550

    shaneisme
    Participant

    Thanks for the clarification :)

    #169551

    jurotek
    Participant

    This reply has been reported for inappropriate content.

    You welcome.
    BTW, I looked over the above DB Schema and something doesn’t feel right about having page_id attribute as object of manual entry in comments table. I don’t see how this could be functionally dependent on comments PK. Shouldn’t this be an FK related to PK in separate table where one would store articles records? It’s just my guess not knowing all the relational constraints or output requirements.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic.

icon-link icon-logo-star icon-search icon-star