Skip to content

Instantly share code, notes, and snippets.

@sunny775

sunny775/MySQL Related Products Query.sql

Forked from tranhunghien/MySQL Related Products Query.sql
Created January 14, 2022 12:37
Show Gist options
  • Save sunny775/4a0efde0e8d1197940010f55577079d2 to your computer and use it in GitHub Desktop.
Save sunny775/4a0efde0e8d1197940010f55577079d2 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @tranhunghien tranhunghien revised this gist Jan 16, 2017. 1 changed file with 7 additions and 0 deletions.
    7 changes: 7 additions & 0 deletions MySQL Related Products Query.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -11,6 +11,13 @@ CREATE TABLE IF NOT EXISTS `products` (
    PRIMARY KEY (`product_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

    /*************************************
    * And add some data to products table:
    **************************************/
    INSERT INTO `products` (`product_id`, `product_title`, `product_description`, `product_price`, `product_sku`, `product_image`,)
    VALUES (1, 'Product 1', 'Product 1 Description', 10.00, '123product123', 'image1.jpg'),
    (2, 'Product 2', 'Product 2 Description', 12.00, '321product321', 'image2.jpg');

    /****************************
    * Then create the tags table:
    *****************************/
  2. @tranhunghien tranhunghien renamed this gist Jan 16, 2017. 1 changed file with 0 additions and 0 deletions.
    0 gistfile1.txt → MySQL Related Products Query.sql
    View file
    File renamed without changes.
  3. @tranhunghien tranhunghien created this gist Jan 16, 2017.
    70 changes: 70 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,70 @@
    /******************************
    * First create a products table:
    *******************************/
    CREATE TABLE IF NOT EXISTS `products` (
    `product_id` int(10) NOT NULL auto_increment,
    `product_title` varchar(255) collate utf8_unicode_ci NOT NULL,
    `product_description` text collate utf8_unicode_ci,
    `product_price` decimal(12,5) NOT NULL,
    `product_sku` varchar(255) collate utf8_unicode_ci NOT NULL,
    `product_image` varchar(255) collate utf8_unicode_ci NOT NULL,
    PRIMARY KEY (`product_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

    /****************************
    * Then create the tags table:
    *****************************/
    CREATE TABLE IF NOT EXISTS `tags` (
    `tag_id` int(10) NOT NULL auto_increment,
    `tag_name` varchar(255) collate utf8_unicode_ci NOT NULL,
    PRIMARY KEY (`tag_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

    /*********************************
    * And add some data to tags table:
    **********************************/
    INSERT INTO `tags` (`tag_id`, `tag_name`) VALUES
    (1, 'Tag1'),
    (2, 'Tag2'),
    (3, 'Tag3'),
    (4, 'Tag4');

    /**********************************************************************
    * Now we need a table to hold connection between the products and tags:
    ***********************************************************************/
    CREATE TABLE IF NOT EXISTS `productsXtags` (
    `productID` int(10) NOT NULL,
    `tagID` int(10) NOT NULL,
    PRIMARY KEY (`productID`,`tagID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    /*************************************************
    * And we add some data to the productsXtags table:
    **************************************************/
    INSERT INTO `productsXtags` (`productID`, `tagID`) VALUES
    (1, 1),
    (1, 2),
    (1, 3),
    (2, 2),
    (2, 3),
    (2, 4);

    /***************************
    * The Related Products Query
    ****************************/
    -- PRODUCT ID IS ID OF THE PRODUCT YOU ARE SHOWING RELATED FOR
    SELECT
    p.*,COUNT(*) AS matchedTags
    FROM products p
    INNER JOIN productsXtags pt ON pt.productID = p.product_id
    WHERE pt.tagID IN(SELECT tagID
    FROM productsXtags
    WHERE productID = PRODUCT_ID)
    AND p.product_id != PRODUCT_ID
    GROUP BY p.product_id
    HAVING COUNT( * ) > 0
    LIMIT 0,3

    /********************************************************************
    * http://leonpanjtar.eu/web-design-blog/mysql-related-products-query/
    *********************************************************************/