join table more than once in the same query?

Question

I'm looking for some help on constructing a query. I have three tables like this:

Products

id    |   (20 other columns I don't need)
1

product_names

product_id    |    product_name
1                  my product
2                  my second product
3                  my third product

cross_sell_products (where each product may have one, two, or no cross sell products)

product_id    |    cross_sell_product_id
1                  2
1                  3

I'm looking to use a single query that will give me a list of all products (with their names), and each of their cross-sell product names.

The problem is, all this information is coming from the product_names table, and I'm not sure how to perform a join more than once on the same table, within the same query. (Hope that make sense!)

So the output would be something like:

id    |    product_name    |    cross_sell_product_1    |    cross_sell_product_2
1          my product           my second product            my third product

Appreciate any help with this!

Answer 1

I believe I figured out a solution to your problem, though I'm not 100% sure it works as you want. Anyway, it's pretty complicated. It's a stored procedure which makes use of a temporary table in order to flip the records. There's a bug in it that I couldn't yet overcome, it caches results from the temporary table. I've tried adding SQL_NO_CACHE in the SELECT statements, but to no effect. Below is the procedure, which may not appear that good here on SO, so you can take a look at it on this GitHub gist too. The gist contains also the structure and data of my test tables. Any bug report or feedback greatly appreciated.

DELIMITER $$

CREATE PROCEDURE `report`()
BEGIN
    DECLARE col_number       INT(2)         DEFAULT 0;
    DECLARE counter          INT(2)         DEFAULT 0;
    DECLARE done             INT(1)         DEFAULT 0;
    DECLARE last_prod        VARCHAR(128)   DEFAULT "";
    DECLARE prod_name        VARCHAR(128);
    DECLARE cross_prod_name  VARCHAR(128);
    DECLARE col_name         VARCHAR(32);
    DECLARE create_temp_tbl  TEXT;

    -- ------------------------------------------------------------------------
    -- Query for fetching products and associated cross products.
    -- ------------------------------------------------------------------------
    DECLARE cross_products CURSOR FOR
        SELECT SQL_NO_CACHE
            b.product_name,
            c.product_name
        FROM cross_sell_products AS a
        INNER JOIN product_names AS b ON
            a.product_id = b.product_id
        INNER JOIN product_names AS c ON
            a.cross_sell_product_id = c.product_id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;


    -- ------------------------------------------------------------------------
    -- Find the largest number of cross products for a single product
    -- ------------------------------------------------------------------------
    SELECT SQL_NO_CACHE
        COUNT(*) AS total INTO col_number
    FROM cross_sell_products
    GROUP BY product_id
    ORDER BY total DESC
    LIMIT 1;


    -- ------------------------------------------------------------------------
    -- Get rid of any instance of report_tmp. Given its structure is changing
    -- from procedure call to procedure call, it might cause problems because
    -- of the different number of columns it has versus the ones that we want
    -- to insert.
    -- ------------------------------------------------------------------------
    DROP TABLE IF EXISTS report_temp;


    -- ------------------------------------------------------------------------
    -- Create a table with as many fields for cross products as the number
    -- stored in col_number (which is the maximum number of cross products for
    -- a single product).
    -- Also, make product_name a primary key. We'll need this later in the
    -- insertion phase.
    -- ------------------------------------------------------------------------
    SET create_temp_tbl = "CREATE TEMPORARY TABLE report_temp (product_name VARCHAR(128) PRIMARY KEY, ";

    WHILE counter < col_number DO
        SET col_name = CONCAT("cross_sel_product_", counter);
        SET create_temp_tbl = CONCAT(create_temp_tbl, CONCAT(col_name, " VARCHAR(128)"));

        IF counter != col_number - 1 THEN
            SET create_temp_tbl = CONCAT(create_temp_tbl, ", ");
        END IF;

        SET counter = counter + 1;
    END WHILE;

    SET @x = CONCAT(create_temp_tbl, ");");

    PREPARE stmt FROM @x;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    TRUNCATE TABLE report_temp;


    -- ------------------------------------------------------------------------
    -- Begin fetch of products and cross products
    -- ------------------------------------------------------------------------
    OPEN cross_products;

    REPEAT
        FETCH cross_products INTO prod_name, cross_prod_name;

        IF NOT done THEN
            -- ----------------------------------------------------------------
            -- Be sure to reset the counter every time the product group is
            -- changing, so that we don't attempt to use more fields than
            -- there are in the temporary table.
            -- ----------------------------------------------------------------
            IF NOT prod_name = last_prod THEN
                SET counter = 0;
                SET last_prod = prod_name;
            END IF;

            -- ----------------------------------------------------------------
            -- For each cross product of a product, try to insert it, in case
            -- it's not the first one in the group a key duplication error will
            -- be reported. In this case, update the entry with a new cross
            -- product.
            -- ----------------------------------------------------------------
            SET col_name     = CONCAT("cross_sel_product_", counter);
            SET @insert_stmt = CONCAT("INSERT INTO report_temp SET"
                                     ," product_name = ?, "
                                     ,  col_name  ," = ? "
                                     ,"ON DUPLICATE KEY UPDATE "
                                     ,  col_name  ," = ?");

            SET @prod_name       = prod_name;
            SET @cross_prod_name = cross_prod_name;

            PREPARE stmt_ins FROM @insert_stmt;
            EXECUTE stmt_ins USING @prod_name, @cross_prod_name, @cross_prod_name;
            DEALLOCATE PREPARE stmt_ins;

            -- Go to next field
            SET counter = counter + 1;
        END IF;
    UNTIL done END REPEAT;

    CLOSE cross_products;

    -- ------------------------------------------------------------------------
    -- Return desired result
    -- ------------------------------------------------------------------------
    SELECT SQL_NO_CACHE * FROM report_temp;
END $$

DELIMITER ;

Answer 2

This is quite tricky, as you are essentially pivoting the cross_sell product table. if you have zero, one, or two cross sell products could you modify your table to have three columns:

id  cross_sell_product_id_1  cross_sell_product_id_2

Then you can do a more simple join as described in the other answers. If not then it is a little tricky to do a query - I'm not a MySQL expert, but I know it on Oracle!

Answer 3

You can add a table more than once by providing an alias for it. For example

SELECT
    *
FROM
    MyTable AS MyTable1
INNER JOIN
    MyTable AS MyTable2
ON
    MyTable1.Id = MyTable2.Id

Answer 4

To do a join between a table and itself, you have to use a self join. This is done by using aliases, e.g. "employee" and "employer" below:

select employee.name employee,
       employer.name manager
  from employees employee,
  join employees employer on employer.name = employee.manager
;

Answer 5

You alias the tables. You can do things like this:

SELECT p.product_name, x.product_name
from products p
inner join cross_sell_products c
   on c.product_id = c.product_id
inner join products x
   on c.product_id = x.product_id;