Reply To: MySQL Joins

[shaneisme]

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!