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;

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

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


SELECT
    category.Id
  , category.Name
  , image_detail.Filename
  , ...
FROM image_detail
INNER JOIN category
ON image_detail.Category_id = category.ID