dpwu16132
dpwu16132
2015-03-17 16:37

INNER JOIN独有的列mysql

已采纳

I have planned to use three tables for my sites tagging system, they looks like:

|-----------------------|
|==> photo              |
|   -> id               |
|   -> custom_id        |
|   -> title            |
|   -> slug             |
|   -> date             |
|   -> image_url        |
|-----------------------|
|==> tags               |
|   -> id               |
|   -> slug             |
|   -> title            |
|-----------------------|
|==> tags_relation      |
|   -> tid              | <-- this is the tags.id
|   -> pid              | <-- this is the photo.custom_id
|-----------------------|

So, to fetch the recent posts of a specific tag i am using INNER JOIN by the following query:

        SELECT p.id, p.custom_id, p.title, p.slug, p.date, p.image_url, t.id, t.slug, tr.* 
        FROM tags t INNER JOIN
            tags_relation tr
            ON t.id = tr.tid INNER JOIN
            photo p
            ON p.custom_id = tr.pid
        WHERE t.slug = 'people' 
        ORDER BY p.date DESC 
        LIMIT 20 

Everything works fine except the fact that the 'slug, id, title' column of the photo table is being replaced by the 'slug, id, title' column of the tags table!
I figured out a solution that is changing the tags columns name, but is there any best practices to solve this problem? Thanks

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

3条回答

  • dongzhang8475 dongzhang8475 6年前

    I think that you should use Aliases. For example:

    SELECT p.id AS Person_Id, p.title AS Person_Title ...
    

    You can learn more about aliases here.

    点赞 评论 复制链接分享
  • doujuncuo9339 doujuncuo9339 6年前

    Our best practice may seem like more work, but we do add unique prefixes to all our column names so you don't get lost in an alias war.

    |-----------------------|
    |==> photo              |
    |   -> photo_id         |
    |   -> photo_custom_id  |
    |   -> photo_title      |
    |   -> photo_slug       |
    |   -> photo_date       |
    |   -> photo_image_url  |
    |-----------------------|
    |==> tags               |
    |   -> tag_id           |
    |   -> tag_slug         |
    |   -> tag_title        |
    |-----------------------|
    |==> tags_relation      |
    |   -> tagRelation_tid  | 
    |   -> tagRelation_pid  | 
    |-----------------------|
    

    Which would change your query to

    SELECT photo_id, photo_custom_id, photo_title, photo_slug, photo_date, photo_image_url, tag_id, tag_slug, tags_relation.* 
        FROM tags INNER JOIN
            tags_relation             
            ON tag_id = tagRelation_tid INNER JOIN
            photo
            ON photo_custom_id = tagRelation_pid
        WHERE tag_slug = 'people' 
        ORDER BY photo_date DESC 
        LIMIT 20 
    

    More verbose, but also more descriptive and when you have many many tables and really really long joins, it works out great...especially when your intellisense kicks in.

    点赞 评论 复制链接分享
  • dragon188199 dragon188199 6年前

    You're going to have to cast some of those fields like this

    SELECT t.column, s.column AS column2
    

    Otherwise, MySQL will indeed pick a field to return (typically the last one with that name) and that's all you get!

    点赞 评论 复制链接分享

相关推荐