SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2009
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Mysql Joining Question

    Hey,

    i wanted to know, if i have the following 2 tables and i wanted to join them, am i right in saying that it would not be possible because the 2 primary keys MUST be the same? but if i had category_id as primary key it would work fine, is that right?


    CREATE TABLE image_detail (
    Category_id INT NOT NULL,
    Filename VARCHAR(255) NOT NULL PRIMARY KEY,
    Type VARCHAR(255) NOT NULL,
    Size TEXT NOT NULL,
    Location VARCHAR(255)NOT NULL,
    Description TEXT NOT NULL
    ) DEFAULT CHARACTER SET utf8;


    CREATE TABLE category (
    Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(255)
    ) DEFAULT CHARACTER SET utf8;

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by nvidia123 View Post
    Hey,

    i wanted to know, if i have the following 2 tables and i wanted to join them, am i right in saying that it would not be possible because the 2 primary keys MUST be the same?
    No, you can join tables on zero (cross join), one or more columns, and none of them has to be the primary key.
    CREATE TABLE image_detail (
    Category_id INT NOT NULL,
    Filename VARCHAR(255) NOT NULL PRIMARY KEY,
    Type VARCHAR(255) NOT NULL,
    Size TEXT NOT NULL,
    Location VARCHAR(255)NOT NULL,
    Description TEXT NOT NULL
    ) DEFAULT CHARACTER SET utf8;


    CREATE TABLE category (
    Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(255)
    ) DEFAULT CHARACTER SET utf8;
    If the two tables are to be joined by category id, then it would be something like
    Code MySQL:
    SELECT
        category.Id
      , category.Name
      , image_detail.Filename
      , ...
    FROM image_detail
    INNER JOIN category
    ON image_detail.Category_id = category.ID

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2009
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    aah i see, cos i did want to join the category id's together, BUT still ensure that filename was the primary key so i naturally thought it was not possible. Thanks that i think that helps


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •