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