INNER JOIN problem

[veledrom]

Hi,

When I run query below I get duplicated records. Instead of 2, I get 4 records. I guess there is a problem with JOIN because when I remove image JOIN, it works fine but without image JOIN, the query is helpless for me.

Please help, thanks in advance

QUERY

SELECT
product.id AS ProductID,
product.code AS ProductCode,
product.description AS ProductDescription,
product.price AS ProductPrice,
product.vat AS ProductVat,
product.is_available AS ProductIsAvailable,
colour.id AS ProductColourID,
colour.colour AS ProductColour,
stock.stock AS ProductStock,
image.id AS ProductImageID,
CONCAT(image.name, '.jpg') AS ProductImage
FROM product
INNER JOIN colour ON product.id = colour.pk_product_id
INNER JOIN stock ON colour.id = stock.pk_colour_id
INNER JOIN image ON product.id = image.pk_product_id

MY DATABASE

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `colour`
-- ----------------------------
DROP TABLE IF EXISTS `colour`;
CREATE TABLE `colour` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `colour` enum('Black','White') NOT NULL COMMENT 'Colour of products',
  `pk_product_id` int(11) NOT NULL COMMENT 'PK of Product table',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_key` (`colour`,`pk_product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of colour
-- ----------------------------
INSERT INTO `colour` VALUES ('1', 'Black', '1');
INSERT INTO `colour` VALUES ('2', 'White', '1');

-- ----------------------------
-- Table structure for `image`
-- ----------------------------
DROP TABLE IF EXISTS `image`;
CREATE TABLE `image` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL COMMENT 'Unique name of image. JPG format',
  `pk_product_id` int(11) NOT NULL COMMENT 'PK of Product table',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of image
-- ----------------------------
INSERT INTO `image` VALUES ('1', '83d8f803-8c4c-11e0-a22a-0ef39922def5', '1');
INSERT INTO `image` VALUES ('2', '10aae9f7-8c4f-11e0-a22a-0ef39922def5', '1');

-- ----------------------------
-- Table structure for `product`
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL COMMENT 'Short code for the product',
  `name` varchar(50) NOT NULL COMMENT 'Name of products',
  `description` text NOT NULL COMMENT 'Detailed information of product',
  `price` decimal(10,2) NOT NULL COMMENT 'Price excludes VAT',
  `vat` decimal(4,2) NOT NULL COMMENT 'VAT',
  `is_available` enum('YES','NO') NOT NULL DEFAULT 'NO' COMMENT 'Defines whether a product available or not',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES ('1', 'TP', 'Pen', 'Description goes here', '1.67', '0.20', 'YES');

-- ----------------------------
-- Table structure for `stock`
-- ----------------------------
DROP TABLE IF EXISTS `stock`;
CREATE TABLE `stock` (
  `stock` int(11) NOT NULL COMMENT 'How many available in stock',
  `pk_colour_id` int(11) NOT NULL COMMENT 'PK of Colour table',
  UNIQUE KEY `unique_key` (`pk_colour_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of stock
-- ----------------------------
INSERT INTO `stock` VALUES ('10', '1');
INSERT INTO `stock` VALUES ('5', '2');

[urtrivedi]

You have 2 records for same (1) pk_product_id in image table.

INSERT INTO `image` VALUES ('1', '83d8f803-8c4c-11e0-a22a-0ef39922def5', '1');INSERT INTO `image` VALUES ('2', '10aae9f7-8c4f-11e0-a22a-0ef39922def5', '1');

[veledrom]

Yes because, it is 1 to M relationship. One product can have many images.

[urtrivedi]

THen its not the fault of query. If your images are multiple then it will show both.

[veledrom]

Yes but why duplicated. It shows 4 records instead of 2

[urtrivedi]

I think problem is in your query`s line number 15 (your first post)

It should read

stock ON product.id = stock.pk_colour_id

instead of

stock ON colour.id = stock.pk_colour_id

[veledrom]

Still same result

[smantscheff]

Your query shows the cartesian product of your tables.
Since you have two colors for the product and two images, the result has to have (at least) 4 rows.
Maybe you can use the group_concat function to pack all product and all image values in only one field which you separate later in processing. Or you delete the color and images clause alltogether if you do not need them in further processing.

[urtrivedi]

adding ot Smantscheff comments, YOu may filter rows by colour

SELECT
product.id AS ProductID,
product.code AS ProductCode,
product.description AS ProductDescription,
product.price AS ProductPrice,
product.vat AS ProductVat,
product.is_available AS ProductIsAvailable,
colour.id AS ProductColourID,
colour.colour AS ProductColour,
stock.stock AS ProductStock,
image.id AS ProductImageID,
CONCAT(image.name, '.jpg') AS ProductImage
FROM product
INNER JOIN colour ON product.id = colour.pk_product_id
INNER JOIN stock ON colour.id = stock.pk_colour_id
INNER JOIN image ON product.id = image.pk_product_id
where colour.colour='Black'

or you may remove color.color from column list

SELECT

product.id AS ProductID,
product.code AS ProductCode,
product.description AS ProductDescription,
product.price AS ProductPrice,
product.vat AS ProductVat,
product.is_available AS ProductIsAvailable,
image.id AS ProductImageID,
CONCAT(image.name, '.jpg') AS ProductImage,
sum(stock.stock) AS ProductStock

FROM product
INNER JOIN colour ON product.id = colour.pk_product_id
INNER JOIN stock ON colour.id = stock.pk_colour_id
INNER JOIN image ON product.id = image.pk_product_id
group by
product.id ,
product.code ,
product.description,
product.price ,
product.vat ,
product.is_available ,
image.id ,
CONCAT(image.name, '.jpg')

[smantscheff]

If you follow in urtridevis direction you will still have to delete the color and image join clauses from your query. Removing the columns from the column list alone does not change the number of result set but only which columns are retrieved from it.

[veledrom]

For my database design, the query is wrong then. As I want to get all those columns, I should use two queries. This is what I understand.

[urtrivedi]

If you follow in urtridevis direction you will still have to delete the color and image join clauses from your query. Removing the columns from the column list alone does not change the number of result set but only which columns are retrieved from it.

I think you have not read my queries. In first I am filtering records by color, and In second I am summing up the stock. So my queries resulting only 2 rows instead of 4.For my database design, the query is wrong then. As I want to get all those columns, I should use two queries. This is what I understand.
First of all if you want all columns then You must find out are you interested in color of product or not. Because color table is the table which is multiplying your output. I think you are not sure what you want to show.

[veledrom]

I read your previous message but I have to return all those columns I've selected in the query. I need all columns in go query. That's why I said I might change the design or run 2 queries.